sql-server - 如何按周顺序显示记录

Question

我有一个包含记录数 12000 的表，现在我需要按周显示，如下所示

ID            Date                   count
1       4 week of November 2014       10
2       1 week of December 2014       120
3       2 week of December 2014       60

我试过

SELECT CONVERT(VARCHAR(150),datepart(day, datediff(day, 0, date)/7 * 7)/7 + 1) 
+ ' week of ' + CONVERT(VARCHAR(150),datename(month,date)) 
+ ' ' + CONVERT(VARCHAR(150),datepart(YEAR,date))
AS DisplayDate, 
COUNT(date) AS LettersCount
FROM tblV400_Leads_Letters 
WHERE DATEDIFF(DAY,date, GETDATE()) <= 28
GROUP BY date

它给出了

 ID            Date                   count
 1    4 week of November 2014          10
 2    2 week of December 2014          74
 3    2 week of December 2014          78
 4    2 week of December 2014          59
 5    1 week of December 2014          79
 6    1 week of December 2014          68
 7    1 week of December 2014          60
 8    1 week of December 2014          68
 9    1 week of December 2014          83

Answer 1

您正在按 date 列分组，这意味着您将为每个不同的 date 值获得一条记录输出。确保 GROUP BY 子句中的表达式与您实际输出的内容完全相同:

SELECT
    CONVERT(VARCHAR(150),datepart(day, datediff(day, 0, date)/7 * 7)/7 + 1) 
+ ' week of ' + CONVERT(VARCHAR(150),datename(month,date)) 
+ ' ' + CONVERT(VARCHAR(150),datepart(YEAR,date))
AS DisplayDate, 
COUNT(date) AS LettersCount
FROM tblV400_Leads_Letters 
WHERE DATEDIFF(DAY,date, GETDATE()) <= 28
GROUP BY
    /* This is simply copied from the SELECT part of the query: */
    CONVERT(VARCHAR(150),datepart(day, datediff(day, 0, date)/7 * 7)/7 + 1) 
+ ' week of ' + CONVERT(VARCHAR(150),datename(month,date)) 
+ ' ' + CONVERT(VARCHAR(150),datepart(YEAR,date))

为避免重复代码，您可以将所有内容包装在子选择中:

SELECT DisplayDate, COUNT(*) FROM (
    SELECT CONVERT(VARCHAR(150),datepart(day, datediff(day, 0, date)/7 * 7)/7 + 1) 
    + ' week of ' + CONVERT(VARCHAR(150),datename(month,date)) 
    + ' ' + CONVERT(VARCHAR(150),datepart(YEAR,date)) AS DisplayDate
    FROM tblV400_Leads_Letters 
    WHERE DATEDIFF(DAY,date, GETDATE()) <= 28
) T
GROUP BY DisplayDate

此外，我不完全确定这段代码的作用:CONVERT(VARCHAR(150),datepart(day, datediff(day, 0, date)/7 * 7)/7 + 1)。但是如果你想获得周数，一个更简单的解决方案是简单地使用 datepart(week, date) 或者 datepart(iso_wk, date) 如果你住在欧洲。