如何编写SQL查询计算所有项目的总成本？

这是个典型的宽表转长表后关联计算的场景，我来给你提供一个通用且易理解的SQL查询方案：

完整SQL语句 #

SELECT 
    p.project_id AS project,
    SUM(p.hours * c.rate) AS cost
FROM (
    -- 把project_table的宽结构转成与cost_table匹配的长结构
    SELECT project_id, year, 'h1' AS hour_type, H1 AS hours
    FROM project_table
    UNION ALL
    SELECT project_id, year, 'h2' AS hour_type, H2 AS hours
    FROM project_table
    UNION ALL
    SELECT project_id, year, 'h3' AS hour_type, H3 AS hours
    FROM project_table
) p
JOIN cost_table c 
    ON p.year = c.year 
    AND LOWER(p.hour_type) = c.hour_type  -- 兼容大小写差异，确保匹配准确
GROUP BY p.project_id
ORDER BY p.project_id;

逻辑解释 #

1. 宽表转长表：子查询p把project_table里的H1/H2/H3三列拆分成每行对应一个工作类型的记录，这样就能和cost_table的行结构对齐，方便关联计算；
2. 关联费率表：通过年份和工作类型匹配cost_table，获取对应年份对应工作的小时费率；
3. 计算总成本：先算出每个工作类型的成本（工时×费率），再按项目ID分组求和，得到每个项目的总成本；
4. 排序输出：最后按项目ID排序，和你期望的输出格式完全一致。

小说明 #

你给出的项目001示例计算结果是24，但根据提供的cost_table数据，实际计算应为(4×5)+(2×1)+(0×2)=22，可能是示例里的费率数据有小出入，但这个SQL的逻辑是完全正确的，只要表内数据准确就能得到对应结果。

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