使用 SQL 按周分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13278552/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
GROUP BY WEEK with SQL
提问by RobVious
I have the following:
我有以下几点：
http://sqlfiddle.com/#!6/226ae/1
http://sqlfiddle.com/#!6/226ae/1
I'm trying to now add one row for each week of the year, and filter the contacts accordingly. CONTACTS has a datetime column. The new table will look like:
我现在尝试为一年中的每一周添加一行，并相应地过滤联系人。CONTACTS 有一个日期时间列。新表将如下所示：
Status 1 Status 2 Status 3
Week 1 3 4 2
Week 2 1 5 3
Week 3 2 2 4
I think that DATEADD needs to be used, however I'm at a loss in terms of how to begin changing my query.
我认为需要使用 DATEADD，但是我不知道如何开始更改我的查询。
I do know that MySQL has a GROUP BY WEEK command, but I don't think that SQL has an equivalent. What's the best way to accomplish this?
我知道 MySQL 有一个 GROUP BY WEEK 命令，但我认为 SQL 没有等价物。实现这一目标的最佳方法是什么？
回答by Taryn
You can use DATEPART(), this groups by both the week and the year in the event you have data spanning multiple years:
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)
Adding the year to the final result:
将年份添加到最终结果中：
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)
回答by Cristian Lupascu
You can use the datepartfunctionto extract the week from a date.
您可以使用该datepart函数从日期中提取星期。
The query becomes:
查询变为：
SELECT datepart(week, created) as 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(week, created)
SqlFiddle: http://sqlfiddle.com/#!6/226ae/6tsq
SqlFiddle：http://sqlfiddle.com/#!6/226ae/6tsq
回答by phanophite
You might try using the group by clause in your query:
您可以尝试在查询中使用 group by 子句：
SELECT
DATE_FORMAT( created, '%u' ) week_number,
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 DATE_FORMAT( created, '%u' )
I'm assuming you are talking about mysql.
我假设你在谈论 mysql。
The DATE_FORMAT function is documented here:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
DATE_FORMAT 函数记录在此处：http:
//dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
