sql - 使用 SQL 按周分组

Question

我有以下几点:

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

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

        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 with Demo

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

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 with demo