如何修改SQL查询，使指定日期区间内无交易的月份总计返回0？

要实现指定日期范围内所有年月都返回统计结果（无交易的月份显示0），核心是先构建包含目标所有年月的维度表，再通过左连接关联交易数据。以下是两种常用实现方式：

方式一：递归CTE生成日期范围 #

适合中等跨度的日期范围，代码简洁直观：

DECLARE @StartDate DATE = '2022-01-01';
DECLARE @EndDate DATE = '2023-12-31';

WITH DateRange AS (
    -- 起始月份的第一天
    SELECT DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1) AS MonthStart
    UNION ALL
    -- 递归生成后续每个月的第一天
    SELECT DATEADD(MONTH, 1, MonthStart)
    FROM DateRange
    WHERE MonthStart < DATEFROMPARTS(YEAR(@EndDate), MONTH(@EndDate), 1)
)
SELECT
    MONTH(dr.MonthStart) AS Month,
    YEAR(dr.MonthStart) AS Year,
    ISNULL(SUM(t.Amount), 0) AS Total
FROM DateRange dr
-- 左连接交易表，确保所有年月都被保留
LEFT JOIN Transactions t 
    ON YEAR(t.Date) = YEAR(dr.MonthStart) 
    AND MONTH(t.Date) = MONTH(dr.MonthStart)
GROUP BY YEAR(dr.MonthStart), MONTH(dr.MonthStart)
ORDER BY Year, Month;

关键说明： #

1. 先通过@StartDate和@EndDate定义统计的时间范围，可根据需求修改。
2. DateRange CTE递归生成每个月的第一天，覆盖整个目标时间区间。
3. 左连接交易表后，用ISNULL(SUM(t.Amount), 0)将无交易月份的NULL转为0。
4. 按年月分组并排序，保证结果有序。

方式二：数字表生成日期范围 #

适合大跨度日期范围（比如10年以上），性能比递归CTE更稳定：

DECLARE @StartDate DATE = '2022-01-01';
DECLARE @EndDate DATE = '2023-12-31';

WITH Numbers AS (
    -- 生成足够数量的连续数字（这里用sys.all_columns作为数据源，也可以用自定义数字表）
    SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1) 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS Num
    FROM sys.all_columns
)
SELECT
    MONTH(DATEADD(MONTH, Num, @StartDate)) AS Month,
    YEAR(DATEADD(MONTH, Num, @StartDate)) AS Year,
    ISNULL(SUM(t.Amount), 0) AS Total
FROM Numbers n
LEFT JOIN Transactions t 
    ON YEAR(t.Date) = YEAR(DATEADD(MONTH, Num, @StartDate))
    AND MONTH(t.Date) = MONTH(DATEADD(MONTH, Num, @StartDate))
GROUP BY YEAR(DATEADD(MONTH, Num, @StartDate)), MONTH(DATEADD(MONTH, Num, @StartDate))
ORDER BY Year, Month;

关键说明： #

1. 通过DATEDIFF(MONTH, @StartDate, @EndDate) + 1计算需要生成的月份数量，确保覆盖整个范围。
2. 利用系统表sys.all_columns生成连续数字（如果有自定义的数字表，替换这里会更高效）。
3. 通过DATEADD(MONTH, Num, @StartDate)生成每个月的日期，逻辑和递归方式一致，但避免了递归开销。

内容的提问来源于stack exchange，提问作者Musaffar Patel