如何为未发生交易的记录补全行并实现累计求和？

解决方案 #

要实现补全无交易记录行，同时让最后一个月份显示累计总额的需求，需要先生成所有客户与月份的完整组合，再关联交易数据，最后通过条件判断区分当月金额和累计总额。修改后的SQL如下：

DROP TABLE IF EXISTS #test_grouping3;
DROP TABLE IF EXISTS #first_day_of_month;

CREATE TABLE #test_grouping3 (
    Customer VARCHAR(1),
    STOTALAMOUNT DECIMAL (10,2),
    UPOSTDATE DATETIME
)

INSERT INTO #test_grouping3
    VALUES ('A', 50.0, '2020-01-01')
INSERT INTO #test_grouping3
    VALUES ('B', 10.0, '2020-02-01')
INSERT INTO #test_grouping3
    VALUES ('A', 25.0, '2020-03-01')

CREATE TABLE #first_day_of_month (
    FirstDateOfMonth DATETIME
)

INSERT INTO #first_day_of_month
    VALUES ('2020-01-01')
INSERT INTO #first_day_of_month
    VALUES ('2020-02-01')
INSERT INTO #first_day_of_month
    VALUES ('2020-03-01')
INSERT INTO #first_day_of_month
    VALUES ('2020-04-01')

-- 核心查询
SELECT
    c.Customer,
    dt.FirstDateOfMonth AS The_UPOSTDATE,
    CASE 
        -- 判断是否是当前客户的最后一个月份
        WHEN dt.FirstDateOfMonth = MAX(dt.FirstDateOfMonth) OVER (PARTITION BY c.Customer)
        THEN SUM(ISNULL(a.STOTALAMOUNT, 0)) OVER (PARTITION BY c.Customer ORDER BY dt.FirstDateOfMonth)
        ELSE ISNULL(a.STOTALAMOUNT, 0)
    END AS TransTotal
FROM (
    -- 获取所有唯一客户
    SELECT DISTINCT Customer FROM #test_grouping3
) c
-- 交叉连接生成每个客户+每个月份的完整记录
CROSS JOIN #first_day_of_month dt
-- 左连接交易表，匹配对应客户和月份的交易数据
LEFT JOIN #test_grouping3 a
    ON c.Customer = a.Customer 
    AND dt.FirstDateOfMonth = a.UPOSTDATE
ORDER BY c.Customer, dt.FirstDateOfMonth;

关键逻辑说明 #

• 补全缺失行：通过CROSS JOIN将所有唯一客户与所有月份做笛卡尔积，确保每个客户的每个月份都有一条记录，彻底解决无交易月份缺失、客户字段为NULL的问题。
• 匹配交易数据：用LEFT JOIN关联原交易表，保留所有完整组合行，无交易的月份用ISNULL将金额转为0。
• 区分当月/累计金额：
  • 用MAX(dt.FirstDateOfMonth) OVER (PARTITION BY c.Customer)获取每个客户的最后一个月份
  • 最后一个月份用窗口函数SUM() OVER(PARTITION BY c.Customer ORDER BY dt.FirstDateOfMonth)计算截至该月的累计总额
  • 其他月份直接显示当月交易金额（无则0）

执行结果 #

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