GROUP BY WEEK与SQL

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有一个等同的.什么是最好的方式来完成这个？

解决方法

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,year(created) year,SUM(case WHEN status = 5 then 1 else 0 end) Status5
FROM contacts
group by  datepart(wk,year(created)

见SQL Fiddle with demo

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!