包括月度计数，包括不存在数据的月份

Question

这似乎应该很容易，但我似乎无法弄清楚。我有一张表，其中包含Artifact名称和Modification_Date等。我有一个查询得到的月份文件修改计数。有几个月没有任何修改。由于我在Modification_Date上查询和分组，因此在没有修改的情况下，这些月份没有返回结果。理想情况下，在结果集中，我希望月份在那里，0为Quantity。

SELECT CONVERT(NVARCHAR(7), Modification_Date, 120) [Month], 
    COUNT(Artifact) as Quantity 
    FROM table 
WHERE Modification_Date > DATEADD(month, -6, getdate()) 
GROUP BY CONVERT(NVARCHAR(7), Modification_Date, 120) 
ORDER BY [Month] DESC 

这让我类似的结果：

Month  Quantity 
-------  -------- 
2013-02  10 
2012-11  12 
2012-10  5 
2012-09  29 

正如你可以看到，2012年12月和2013年1月都没有在结果集中。我想让那些月份在那里代表0 Quantity，这样我就可以在SQL报告条形图中使用这些数据，并使这些月份以0值表示。目前在条形图上它完全跳过了这几个月。有没有办法在过去6个月内生成yyyy-mmMonth列而不是仅仅使用Modification_Date？

Answer 1

不要将日期转换为字符串来剥离时间或日期，请使用日期算术。转换为字符串效率较低，还需要扫描整个表。

如果您要转换为固定长度的字符串，请不要使用NVARCHAR，请使用CHAR。在数字日期你需要支持哪些Unicode字符？变音？磅的标志？象形文字？

下面是一个使用目录视图生成6行然后从前6个月减去当前日期组到月份的示例（和当前方法不同，应使用Modification_Date上的索引）。这在第一次看到它时并不完全直观，但是您可以看到我的系列产品不带环路（part 1 | part 2 | part 3）。

;WITH x(m) AS 
(
    SELECT TOP 6 DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) 
    - (ROW_NUMBER() OVER (ORDER BY [object_id])), 0) 
    FROM sys.all_objects 
    ORDER BY [object_id] 
) 
SELECT [Month] = x.m, Quantity = COALESCE(COUNT(t.Artifact), 0) 
FROM x 
LEFT OUTER JOIN dbo.tablename AS t 
ON t.Modification_Date >= x.m 
AND t.Modification_Date < DATEADD(MONTH, 1, x.m) 
GROUP BY x.m 
ORDER BY x.m DESC; 

请注意，这不包括当前月份。如果你想转移到包括日 - >月，而不是年9月 - >二月，只是改变这一行：

+ 1 - (ROW_NUMBER() OVER (ORDER BY [object_id])), 0) 

如果格式化为YYYY-MM是绝对必要的，你可以这样做：

;WITH y(m) AS 
(
    SELECT TOP 6 DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) 
    - (ROW_NUMBER() OVER (ORDER BY [object_id])), 0) 
    FROM sys.all_objects 
    ORDER BY [object_id] 
), 
x([Month], Quantity) 
AS 
(
    SELECT [Month] = y.m, Quantity = COALESCE(COUNT(t.Artifact), 0) 
    FROM y 
    LEFT OUTER JOIN dbo.tablename AS t 
    ON t.Modification_Date >= y.m 
    AND t.Modification_Date < DATEADD(MONTH, 1, y.m) 
    GROUP BY y.m 
) 
SELECT [Month] = CONVERT(CHAR(7), [Month], 120), Quantity 
FROM x 
ORDER BY [Month] DESC;