代码之家 › 专栏 › 技术社区 › user3369545

按特定列的标题计数

func join loops sql-server sql

-4

user3369545 · 技术社区 · 6 年前

Date       ID       intent 
9/1/2018    1   fetch it 
9/1/2018    1   Ask it
9/1/2018    1   default
9/1/2018    2   remand
9/1/2018    2   choose it
9/2/2018    1   fetch it 
9/2/2018    1   choose 
9/2/2018    1   default
9/3/2018    4   Ask it
9/3/2018    4   fetch it 
9/4/2018    5   hello
9/4/2018    1   call
9/4/2018    1   default
9/5/2018    1   remand
9/5/2018    1   default
9/5/2018    2   fetch it 
9/5/2018    2   default

对于上表:我试图得到以下输出…你能在这里帮助。。。

9/1/2018    1
9/2/2018    1
9/3/2018    0
9/4/2018    1
9/5/2018    2

每天都有默认的意图的意图的名称

9/1/2018    ask it
9/2/2018    choose
9/3/2018    
9/4/2018    call
9/5/2018    remand 
9/5/2018    fetch it

每天都有默认的ID计数

9/1/2018    1
9/2/2018    1
9/3/2018    0
9/5/2018    2

谢谢。。。

3 回复 | 直到 6 年前

swePeso 6 年前

    DECLARE @Sample TABLE
    (
        theDate DATE NOT NULL,
        ID INT NOT NULL,
        Intent VARCHAR(20) NOT NULL
    );

INSERT  @Sample
VALUES  ('20180901', 1, 'fetch it'),
    ('20180901', 1, 'Ask it'),
    ('20180901', 1, 'default'),
    ('20180901', 2, 'remand'),
    ('20180901', 2, 'choose it'),
    ('20180902', 1, 'fetch it'),
    ('20180902', 1, 'choose'),
    ('20180902', 1, 'default'),
    ('20180903', 4, 'Ask it'),
    ('20180903', 4, 'fetch it'),
    ('20180904', 5, 'hello'),
    ('20180904', 1, 'call'),
    ('20180904', 1, 'default'),
    ('20180905', 1, 'remand'),
    ('20180905', 1, 'default'),
    ('20180905', 2, 'fetch it'),
    ('20180905', 2, 'default');

-- swePeso
WITH cteData(theDate, Task1, Task2, Task3)
AS (
    SELECT      theDate,
            SUM(CASE WHEN Intent = 'default' THEN 1 ELSE 0 END) AS Task1,
            MIN(CASE WHEN Intent = 'default' THEN NULL ELSE Intent END) AS Task2,
            MAX(CASE WHEN Intent = 'default' THEN ID ELSE NULL END) AS Task3
    FROM        @Sample
    GROUP BY    theDate,
            ID
)
SELECT      theDate,
        SUM(Task1) AS Task1,
        MAX(CASE WHEN Task1 = 1 THEN Task2 ELSE '' END) AS Task2,
        COUNT(DISTINCT Task3) AS Task3
FROM        cteData
GROUP BY    theDate
ORDER BY    theDate;

Yogesh Sharma 6 年前

我将使用第一个示例输出:

select Date, sum(case when intent = 'default' then 1 else 0 end) 
from table t
group by Date;

Sahi 6 年前

这有用吗。?

    Create Table #tmp([Date] Date,ID int, intent Varchar(10))

    Insert into #tmp
    SELECT '9/1/2018',1,'fetch it' Union All
    SELECT '9/1/2018',1,'Ask it' Union All
    SELECT '9/1/2018',1,'default' Union All
    SELECT '9/1/2018',2,'remand' Union All
    SELECT '9/1/2018',2,'choose it' Union All
    SELECT '9/2/2018',1,'fetch it' Union All
    SELECT '9/2/2018',1,'choose' Union All
    SELECT '9/2/2018',1,'default' Union All
    SELECT '9/3/2018',4,'Ask it' Union All
    SELECT '9/3/2018',4,'fetch it' Union All
    SELECT '9/4/2018',5,'hello' Union All
    SELECT '9/4/2018',1,'call' Union All
    SELECT '9/4/2018',1,'default' Union All
    SELECT '9/5/2018',1,'remand'Union All
    SELECT '9/5/2018',1,'default' Union All
    SELECT '9/5/2018',2,'fetch it' Union All
    SELECT '9/5/2018',2,'default' 


    ;with cte
    As
    (
        Select *,LEAD(intent) over(partition by [Date] order by (Select null)) as FollowingIntent
        from #tmp   
    )

    Select Date, SUM(CASE WHEN FollowingIntent='default' then 1 else 0 end)
    from cte
    Group by [Date]

    Drop Table #tmp