如何编写SQL查询获取2020年各客户月度累计支付额

要实现你的需求，需要覆盖三个核心点：生成2020年所有月份、确保每个客户每个月都有记录、计算累计支付并处理无支付的情况。以下是完整的SQL实现：

完整SQL代码 #

WITH months AS (
    -- 生成2020年1-12月的月份列表
    SELECT 1 AS month
    UNION ALL
    SELECT month + 1 FROM months WHERE month < 12
),
customer_month_pairs AS (
    -- 生成所有客户与所有月份的组合，避免遗漏无支付的月份
    SELECT c.customer, c.customer_name, m.month
    FROM Customers c
    CROSS JOIN months m
),
cumulative_calculations AS (
    SELECT
        cmp.customer_name,
        cmp.month,
        -- 计算截至当前月的累计支付总额
        COALESCE(SUM(p.sum_payment), 0) OVER (
            PARTITION BY cmp.customer 
            ORDER BY cmp.month
        ) AS total_cumulative,
        -- 判断当月是否有支付记录
        CASE WHEN EXISTS (
            SELECT 1 FROM Payments p
            WHERE p.customer = cmp.customer
              AND YEAR(p.date) = 2020
              AND MONTH(p.date) = cmp.month
        ) THEN 1 ELSE 0 END AS has_monthly_payment
    FROM customer_month_pairs cmp
    LEFT JOIN Payments p
        ON cmp.customer = p.customer
        AND YEAR(p.date) = 2020
        AND MONTH(p.date) <= cmp.month
    GROUP BY cmp.customer, cmp.customer_name, cmp.month
)
-- 最终输出，处理无支付记录的显示逻辑
SELECT
    month AS 月份,
    customer_name AS 客户姓名,
    CASE 
        WHEN has_monthly_payment = 0 THEN 'payments does not exist'
        ELSE CAST(total_cumulative AS VARCHAR) 
    END AS 累计支付总额
FROM cumulative_calculations
ORDER BY customer_name, month;

关键逻辑说明 #

1. 生成月份列表：用递归CTE快速生成12个月份，确保2020年每个月都被包含。
2. 客户-月份全组合：通过CROSS JOIN把所有客户和月份配对，这是保证无支付月份也能显示的核心。
3. 累计支付计算：使用窗口函数SUM() OVER(PARTITION BY ... ORDER BY ...)，按客户分组、月份排序，自动计算截至当月的累计支付额；COALESCE处理无任何支付时的NULL值，转为0。
4. 无支付记录判断：用EXISTS子查询检查该客户当月是否有支付记录，再通过CASE语句替换为要求的文本。

部分输出示例 #

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