SQL如何补全查询缺失月份并将对应TotalPayment显示为0

问题背景 #

需要补全查询结果中缺失的月份，缺失月份对应的TotalPayment字段显示为0，此前使用row_number方法未实现需求。

现有查询语句 #

select brgy,
       datename(month,month_dt) as month_dt,
       TotalPayment
from brgypayment
order by brgy, month(month_dt)

当前查询结果 #

brgy month_dt TotalPayment
barangay1 January 5345.00
barangay1 February 34232.00
barangay1 March 53454.00
barangay1 April 25234.00
barangay1 May 43224.00
barangay1 June 14.00
barangay1 July 141.00
barangay1 August 4415.00
barangay1 September 5455.00
barangay1 October 1235.00
barangay1 November 53535.00
barangay1 December 1661.00
barangay2 January 97859.00
barangay2 February 5673.00
barangay2 March 78764.00
barangay2 April 84673.00
barangay2 May 63468.00
barangay2 June 6365.00
barangay2 July 2145.00
barangay2 August 4415.00
barangay2 September 62652.00
barangay2 October 24521.00
barangay3 January 52345.00
barangay3 February 15454.00

期望查询结果 #

brgy month_dt TotalPayment
barangay1 January 5345.00
barangay1 February 34232.00
barangay1 March 53454.00
barangay1 April 25234.00
barangay1 May 43224.00
barangay1 June 14.00
barangay1 July 141.00
barangay1 August 4415.00
barangay1 September 5455.00
barangay1 October 1235.00
barangay1 November 53535.00
barangay1 December 1661.00
barangay2 January 97859.00
barangay2 February 5673.00
barangay2 March 78764.00
barangay2 April 84673.00
barangay2 May 63468.00
barangay2 June 6365.00
barangay2 July 2145.00
barangay2 August 4415.00
barangay2 September 62652.00
barangay2 October 24521.00
barangay2 November 0.00
barangay2 December 0.00
barangay3 January 52345.00
barangay3 February 15454.00
barangay3 March 0.00
barangay3 April 0.00
barangay3 May 0.00
barangay3 June 0.00
barangay3 July 0.00
barangay3 August 0.00
barangay3 September 0.00
barangay3 October 0.00
barangay3 November 0.00
barangay3 December 0.00

实现代码 #

（注：以下代码适配SQL Server语法，核心逻辑是生成全量的社区+月份基准表，再左连原始支付数据）

WITH Months AS (
    -- 生成1-12月的序列
    SELECT 1 AS month_num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
    SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
),
AllBrgys AS (
    -- 获取所有不重复的社区列表
    SELECT DISTINCT brgy FROM brgypayment
),
FullBase AS (
    -- 生成每个社区对应12个月的全量基准
    SELECT a.brgy, m.month_num, DATENAME(month, DATEFROMPARTS(2023, m.month_num, 1)) AS month_dt
    FROM AllBrgys a CROSS JOIN Months m
)
SELECT 
    f.brgy,
    f.month_dt,
    COALESCE(b.TotalPayment, 0.00) AS TotalPayment
FROM FullBase f
LEFT JOIN brgypayment b 
    ON f.brgy = b.brgy 
    AND MONTH(b.month_dt) = f.month_num
ORDER BY f.brgy, f.month_num

逻辑说明 #

1. 先通过CTE生成1到12的月份序列，覆盖全年所有月份
2. 提取所有不重复的社区（brgy）列表，确保每个社区都能匹配到12个月份
3. 社区列表和月份序列做交叉连接，得到每个社区对应全年12个月的全量基准数据
4. 全量基准表左连接原始支付表，通过社区和月份维度匹配数据
5. 用COALESCE函数将未匹配到的支付金额默认赋值为0.00，保证缺失月份的金额正确显示

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