如何基于关联另一表的两列聚合数据并按事件ID汇总成本？

嘿，很高兴能帮你搞定这个数据聚合的问题！咱们一步步来拆解：

当你需要从两个都关联到同一张主表的外键列聚合数据时，核心思路是把两个外键列转换成统一的单列数据集，再和主表关联后进行聚合。常用的做法是用UNION ALL将两个列的记录合并（这里一定要用UNION ALL而不是UNION，后者会自动去重，可能导致你丢失原本需要统计的重复数据），之后就可以像处理普通单列数据一样分组计算总和、平均值等聚合指标了。

根据你提供的建表语句，要汇总每个事件（不管它是作为crime_incidentid还是similar_incidentid出现在listofincidents表中）的cost_to_city总和，我们可以用子查询结合UNION ALL来实现：

SELECT 
    ci.id, 
    ci.name, 
    COALESCE(SUM(sub.cost_to_city), 0) AS total_cost_to_city
FROM crimeincidents ci
LEFT JOIN (
    -- 把两个外键列转换成统一的incident_id列，保留对应的成本值
    SELECT crime_incidentid AS incident_id, cost_to_city FROM listofincidents
    UNION ALL
    SELECT similar_incidentid AS incident_id, cost_to_city FROM listofincidents
) sub ON ci.id = sub.incident_id
GROUP BY ci.id, ci.name
ORDER BY total_cost_to_city DESC;

这段代码的关键点： #

• 子查询里的UNION ALL会把listofincidents中每条记录的两个关联事件ID都拆成独立的行，这样每个事件ID不管出现在哪个外键列里，都会被纳入统计范围。
• 用LEFT JOIN关联主表crimeincidents，确保即使某个事件没有任何关联的成本记录，也会被显示出来（此时COALESCE会把空的求和结果转换成0，避免出现NULL）。
• 最后按事件ID和名称分组，用SUM计算总成本，再按总成本倒序排列，方便你快速看到成本最高的事件。

如果不需要显示主表的name字段，也可以简化成只针对关联数据的聚合：

SELECT 
    incident_id, 
    SUM(cost_to_city) AS total_cost_to_city
FROM (
    SELECT crime_incidentid AS incident_id, cost_to_city FROM listofincidents
    UNION ALL
    SELECT similar_incidentid AS incident_id, cost_to_city FROM listofincidents
) sub
GROUP BY incident_id
ORDER BY total_cost_to_city DESC;

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