如何用SQL计算收款金额占总金额的百分比并生成收支报表？

问题分析 #

你需要的报表包含三部分核心内容：单条收款记录的明细及占比、对应公司的同期支付总额、汇总总计行。之前的语句错误在于在非聚合查询中嵌套了双层SUM窗口函数，单条收款记录的SUM(valuer)无意义，且全局总收款的计算逻辑未匹配场景。

实现SQL语句 #

WITH period_params AS (
    -- 统一管理查询时间段，便于后续修改
    SELECT '2022-10-01' AS start_date, '2022-10-30' AS end_date
),
total_receive AS (
    -- 计算指定时间段内的总收款金额
    SELECT SUM(valuer) AS total_valuer
    FROM Receive, period_params
    WHERE issuedate BETWEEN start_date AND end_date
),
company_pay_summary AS (
    -- 计算各公司指定时间段内的总应付款
    SELECT 
        company,
        SUM(valuep) AS total_pay
    FROM Pay, period_params
    WHERE dueday BETWEEN start_date AND end_date
    GROUP BY company
)
-- 主查询：获取每条收款记录的明细、占比及对应公司支付额
SELECT 
    r.idconta,
    r.company,
    r.issuedate,
    r.valuer,
    -- 计算单条收款占总收款的百分比，保留2位小数
    ROUND((r.valuer * 100.0 / tr.total_valuer), 2) AS percentage,
    COALESCE(cps.total_pay, 0) AS total_pay -- 处理无支付记录的公司，显示0
FROM Receive r
CROSS JOIN total_receive tr
LEFT JOIN company_pay_summary cps ON r.company = cps.company,
period_params pp
WHERE r.issuedate BETWEEN pp.start_date AND pp.end_date
-- 追加总计行
UNION ALL
SELECT 
    '总计' AS idconta,
    '所有公司' AS company,
    NULL AS issuedate,
    tr.total_valuer AS valuer,
    100.0 AS percentage,
    COALESCE(SUM(cps.total_pay), 0) AS total_pay
FROM total_receive tr
LEFT JOIN company_pay_summary cps ON 1=1;

关键说明 #

1. 时间段统一管理：用period_params CTE封装起止日期，避免多处修改。
2. 全局总收款计算：通过total_receive预计算总收款，避免每条记录重复计算，提升效率。
3. 公司支付金额关联：用company_pay_summary预先聚合各公司支付总额，再与收款记录关联，避免重复聚合。
4. 空值处理：用COALESCE确保无支付记录的公司显示0，避免报表出现NULL。
5. 总计行生成：通过UNION ALL追加汇总行，总收款占比固定为100%，总支付为所有公司支付额之和。

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