SQL 查询返回按周、月和年分组的记录。没有记录的周数应返回 0

Question

鉴于以下数据:

ID      CreatedDate
ID1     2014-06-04 01:40:56.880
ID8     2014-06-05 00:27:02.403
ID6     2014-06-04 01:51:47.060
ID7     2014-06-05 00:25:35.187
ID4     2014-06-04 01:48:44.157
ID10    2014-06-05 00:28:33.993
ID43    2014-06-16 05:17:18.803
ID72    2014-06-20 04:00:07.733
.
.
.
Etc.

我需要编写一个查询，按周、月和年对记录进行分组。需要返回特定一周内创建的记录数。输出应如下所示:

Week    Month   Year    Count
23         6    2014    16
24         6    2014    0
25         6    2014    7
26         6    2014    0
27         7    2014    25
28         7    2014    18  
.
.
.
etc.

如果一周没有记录，则计数应返回 0。仅应返回自执行查询以来过去 12 个月内创建的记录。该查询将每周执行一次以生成报告。

Answer 1

演示目的我使用表名称为 [DBO].[FACTINTERNETSALES] 和列 [ORDERDATE]对于您来说，列将为 [CreatedDate]。仅供引用，这是在 SQL Server 中完成的。

DECLARE @START INT,
        @END INT

SELECT @START   = CAST(CAST(MIN(ORDERDATE) AS DATETIME) AS INT) FROM [DBO].[FACTINTERNETSALES] 
SELECT @END     = CAST(CAST(MAX(ORDERDATE) AS DATETIME) AS INT) FROM [DBO].[FACTINTERNETSALES] 

--SELECT @START,@END

DECLARE @DATEDIM TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, DATED DATETIME)
WHILE @START <= @END
BEGIN
    INSERT INTO @DATEDIM VALUES (CAST(@START AS DATETIME))
    SET @START = @START  + 1 
END

SELECT      LU.WEEK,LU.MONTH,LU.YEAR,ISNULL(MAIN.COUNT,0) [COUNT]
FROM        (
            SELECT      DATEPART(WEEK,DATED) [WEEK],DATEPART(MONTH,DATED) [MONTH],DATEPART(YEAR,DATED) [YEAR] 
            FROM        @DATEDIM
            GROUP BY    DATEPART(WEEK,DATED),DATEPART(MONTH,DATED),DATEPART(YEAR,DATED)) LU
LEFT JOIN
            (
            SELECT  [YEAR],[MONTH],[WEEK],COUNT(*) [COUNT] FROM
            (SELECT DATEPART(YEAR,ORDERDATE) [YEAR],
                    DATEPART(MONTH,ORDERDATE) [MONTH],
                    DATEPART(WEEK,ORDERDATE) [WEEK]
            FROM    [DBO].[FACTINTERNETSALES] ) LUINNER
            GROUP BY [YEAR],[MONTH],[WEEK]) MAIN 
ON
            LU.MONTH = MAIN.MONTH AND
            LU.YEAR = MAIN.YEAR AND
            LU.WEEK = MAIN.WEEK
ORDER BY    LU.YEAR,LU.MONTH,LU.WEEK

结果:(仅结果的一部分)