MySQL技术求助：统计各组织来自学生及捐赠者的筹款总额

嘿，看起来你卡在合并两个表的筹款数据按组织汇总的环节了！首先我注意到你贴的DONORS表创建语句没写完，我先合理假设这个表包含OrgID（用来关联对应的组织）和AmountContributed字段——毕竟要按组织汇总，这两个字段是必须的。

接下来给你两种实用的查询方案，你可以根据自己的需求选：

方案一：分别汇总再合并 #

先单独计算每个组织来自学生和捐赠者的金额，再通过OrgID关联合并，逻辑清晰，也方便排查问题：

SELECT
    COALESCE(s.OrgID, d.OrgID) AS OrgID,
    COALESCE(s.TotalStudentRaised, 0) + COALESCE(d.TotalDonorContributed, 0) AS TotalFundsRaised
FROM
    (SELECT OrgID, SUM(AmountRaised) AS TotalStudentRaised
     FROM STUDENT
     GROUP BY OrgID) s
FULL OUTER JOIN
    (SELECT OrgID, SUM(AmountContributed) AS TotalDonorContributed
     FROM DONORS
     GROUP BY OrgID) d ON s.OrgID = d.OrgID;

关键说明： #

• COALESCE函数用来处理某组织只有一种筹款来源的情况，避免出现NULL值导致总额计算错误
• FULL OUTER JOIN确保所有有筹款记录的组织都被包含，不管它只有学生捐款还是只有捐赠者捐款

方案二：先合并记录再汇总 #

把两个表的筹款记录先通过UNION ALL合并，再按组织分组求和，代码更简洁：

SELECT
    OrgID,
    SUM(TotalAmount) AS TotalFundsRaised
FROM
    (SELECT OrgID, AmountRaised AS TotalAmount FROM STUDENT
     UNION ALL
     SELECT OrgID, AmountContributed AS TotalAmount FROM DONORS) combined
GROUP BY OrgID;

关键说明： #

• UNION ALL不会去重，保留所有原始筹款记录，确保求和准确（注意：如果用UNION会丢失重复的筹款记录，导致金额少算）
• 这种方式自动包含所有有筹款的组织，不需要额外处理NULL

针对MySQL的额外适配 #

如果你的MySQL版本不支持FULL OUTER JOIN（MySQL 8.0之前的版本确实不支持），可以用LEFT JOIN加UNION来替代方案一：

SELECT
    s.OrgID,
    COALESCE(s.TotalStudentRaised, 0) + COALESCE(d.TotalDonorContributed, 0) AS TotalFundsRaised
FROM
    (SELECT OrgID, SUM(AmountRaised) AS TotalStudentRaised FROM STUDENT GROUP BY OrgID) s
LEFT JOIN
    (SELECT OrgID, SUM(AmountContributed) AS TotalDonorContributed FROM DONORS GROUP BY OrgID) d ON s.OrgID = d.OrgID
UNION
SELECT
    d.OrgID,
    COALESCE(s.TotalStudentRaised, 0) + COALESCE(d.TotalDonorContributed, 0) AS TotalFundsRaised
FROM
    (SELECT OrgID, SUM(AmountContributed) AS TotalDonorContributed FROM DONORS GROUP BY OrgID) d
LEFT JOIN
    (SELECT OrgID, SUM(AmountRaised) AS TotalStudentRaised FROM STUDENT GROUP BY OrgID) s ON d.OrgID = s.OrgID;

另外，如果有专门的ORGANIZATION表（存储所有组织信息），可以把查询和它关联，这样能列出所有组织，包括筹款为0的组织：

SELECT
    o.OrgID,
    o.OrgName,
    COALESCE(combined.TotalFundsRaised, 0) AS TotalFundsRaised
FROM
    ORGANIZATION o
LEFT JOIN
    (SELECT OrgID, SUM(TotalAmount) AS TotalFundsRaised
     FROM (SELECT OrgID, AmountRaised AS TotalAmount FROM STUDENT
           UNION ALL
           SELECT OrgID, AmountContributed AS TotalAmount FROM DONORS) combined
     GROUP BY OrgID) combined ON o.OrgID = combined.OrgID;

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