SQL查询需求：对比慈善团体承诺捐赠总额与实际付款总额

我正在开发一个慈善网站，个人捐赠承诺金额存在Pledged表，这些个人归属于不同团体；实际到账付款记录在Payment表，付款只关联团体负责人，允许多次付款累计达成承诺总额。现有三张数据表结构及示例数据如下：

数据表结构与示例数据 #

Donor表（捐赠人信息） #

Pledged表（个人捐赠承诺） #

Payment表（实际付款记录） #

现有问题与查询 #

我已经写了两个求和查询，但不知道如何整合它们，来对比每个团体的承诺捐赠总额与对应负责人的实际付款总额，以此验证承诺是否已付清。现有查询如下：

query1（计算团体承诺总额，原查询存在语法错误） #

SELECT Donor.Name Donor.id , SUM(Pledged.amount) 
FROM Donor , Pledged
WHERE Donor.id = Pledged.id
AND year = '2023'
GROUP BY group_leader_id

query2（计算负责人付款总额，原查询存在关联错误） #

SELECT Donor.id , SUM(Payment.amount) 
FROM Payment
WHERE year = '2023'
GROUP BY Donor.id

修正与整合后的查询 #

首先修正原查询的语法错误，再将两个统计结果通过团体负责人ID关联，最终得到包含承诺总额、付款总额及付款状态的对比数据：

-- 先计算每个团体的总承诺金额
WITH GroupPledged AS (
    SELECT 
        d.group_leader_id AS leader_id,
        MAX(d.name) AS leader_name,
        SUM(p.amount) AS total_pledged
    FROM Donor d
    JOIN Pledged p ON d.id = p.id
    WHERE p.year = 2023
    GROUP BY d.group_leader_id
),
-- 再计算每个负责人的总付款金额
LeaderPayment AS (
    SELECT 
        id AS leader_id,
        SUM(amount) AS total_paid
    FROM Payment
    WHERE year = 2023
    GROUP BY id
)
-- 关联两个统计结果，对比并判断付款状态
SELECT 
    gp.leader_id,
    gp.leader_name,
    gp.total_pledged,
    COALESCE(lp.total_paid, 0) AS total_paid,
    CASE 
        WHEN gp.total_pledged <= COALESCE(lp.total_paid, 0) THEN '已付清'
        ELSE '未付清'
    END AS payment_status
FROM GroupPledged gp
LEFT JOIN LeaderPayment lp ON gp.leader_id = lp.leader_id;

查询结果说明 #

以示例数据为例，运行后会得到如下结果：

关键修正点 #

1. 原query1中SELECT Donor.Name Donor.id语法错误，调整为正确的字段选择与别名；使用显式JOIN替代隐式连接，提升可读性。
2. 原query2中错误引用Donor.id，实际Payment表的id就是负责人ID，直接使用即可。
3. 使用CTE（公共表表达式）拆分统计逻辑，让代码更清晰；用COALESCE处理无付款记录的情况，避免出现NULL值。

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