按SQL分组

Question

我有以下几点：

http://sqlfiddle.com/#!6/226ae/1

我现在尝试为一年中的每个星期添加一行，并相应地过滤联系人。CONTACTS有一个datetime列。新表将如下所示：

        Status 1    Status 2    Status 3
Week 1      3          4           2
Week 2      1          5           3
Week 3      2          2           4

我认为需要使用DATEADD，但是在如何开始更改查询方面我迷茫了。

我确实知道MySQL具有GROUP BY WEEK命令，但我认为SQL没有等效的命令。做到这一点的最佳方法是什么？

Answer 1

DATEPART()如果您具有跨多年的数据，则可以使用，按星期和年份进行分组：

SELECT 
    'Week ' + cast(datepart(wk, created) as varchar(2)) Week,
    SUM(case WHEN status = 1 then 1 else 0 end) Status1,
    SUM(case WHEN status = 2 then 1 else 0 end) Status2,
    SUM(case WHEN status = 3 then 1 else 0 end) Status3,
    SUM(case WHEN status = 4 then 1 else 0 end) Status4,
    SUM(case WHEN status = 5 then 1 else 0 end) Status5
FROM contacts
group by datepart(wk, created), year(created)

参见带有演示的SQL Fiddle

将年份添加到最终结果中：

SELECT 
    'Week ' + cast(datepart(wk, created) as varchar(2)) Week,
    year(created) year,
    SUM(case WHEN status = 1 then 1 else 0 end) Status1,
    SUM(case WHEN status = 2 then 1 else 0 end) Status2,
    SUM(case WHEN status = 3 then 1 else 0 end) Status3,
    SUM(case WHEN status = 4 then 1 else 0 end) Status4,
    SUM(case WHEN status = 5 then 1 else 0 end) Status5
FROM contacts
group by  datepart(wk, created), year(created)

参见带有演示的SQL Fiddle