SQL:报告不存在的内容

Question

我有一个查询，它按月搜索错误原因和错误原因的计数。

Select datepart(month,CL.[errorDate]) As Month,
ErrorReason As 'Errors'
Count(ErrorReason)
From ErrorTable
Group BY datepart(month,errorDate), ErrorReason

这很好。如果我有10个错误原因

Reason 1
Reason 2
Reason 3
... and so on

一个月以来，原因2没有出现任何错误。那么它就不会返回结果。

我如何更改此查询，使其返回月份编号、错误原因，但错误计数为0

感谢

Answer 1

您可以为此使用日期表。通过提供每个月的记录（无论错误数量如何），有一个要加入的日期列表可以为您提供所需的所有值。

在这个例子中，我使用递归CTE动态创建了一个日期表。但一张实体桌子也同样有效。我还创建了第二个CTE，其中包含一些示例数据，这样任何人都可以运行该示例。

示例

DECLARE @StartMonth DATE = '2015-01-01';
DECLARE @EndMonth    DATE = '2015-03-01';
WITH SampleError AS
    (
        /* This CTE returns sample error records.
         * There are two entrie for Jan and Mar.
         * None for Feb.
         */
        SELECT
            r.ErrorDate,
            r.ErrorReason
        FROM
            (
                VALUES
                    ('2015-01-01', 1),
                    ('2015-01-02', 1),
                    ('2015-03-01', 1),
                    ('2015-03-02', 2)
            ) AS r(ErrorDate, ErrorReason)
    ),
    DateTable AS
    (
        /* This CTE creates a date table.
         * You could replace with with a 
         * real table.
         * It uses recurision to populate the rows.
         */
            SELECT
                @StartMonth        AS [Month]
        UNION ALL
            SELECT
                DATEADD(MONTH, 1, [Month])        AS [Month]
            FROM
                DateTable
            WHERE
                [Month] < @EndMonth
    )
SELECT
    dt.[Month],
    SUM(CASE WHEN se.ErrorReason = 1 THEN 1 ELSE 0 END)        AS Error1Count,
    SUM(CASE WHEN se.ErrorReason = 2 THEN 1 ELSE 0 END)        AS Error2Count
FROM
    DateTable AS dt
        LEFT OUTER JOIN SampleError AS se        ON    MONTH(se.ErrorDate)    = MONTH(dt.[Month])
                                                AND    YEAR(se.ErrorDate)    = YEAR(dt.[Month])
GROUP BY
    dt.[Month]
;

Answer 2

SELECT m.MonthName, r.ErrorReason,
    (SELECT COUNT(*) 
    FROM ErrorTable e2 
    WHERE datepart(month,e2.errorDate) = m.MonthName AND e2.ErrorReason = r.ErrorReason)
FROM (
    SELECT datepart(month,errorDate) As MonthName
    FROM ErrorTable
    GROUP BY datepart(month,errorDate)
) m CROSS JOIN
(   
    Select ErrorReason
    FROM ErrorTable
    GROUP BY ErrorReason
) r