如何在已排序的PROJECTSWORKS查询中新增子工作总成本列？

我拥有三张表：

1. PROJECTSWORKS表（项目工作与子工作） #

2. ACTIVITIES表（工作活动） #

3. ACTIVITY_COSTS表（活动成本） #

我已经写了一段SQL，用来按工作与子工作的顺序对PROJECTSWORKS表排序：

SELECT 
    a.WORK_ID, a.MAIN_WORK_ID, a.WORK_NAME
FROM 
    PROJECTSWORKS a  
WHERE 
    a.PROJECT_ID = 1
ORDER BY 
    CASE 
        WHEN a.WORK_ID = a.MAIN_WORK_ID THEN a.MAIN_WORK_ID
        WHEN a.WORK_ID < a.MAIN_WORK_ID THEN a.WORK_ID
        WHEN a.WORK_ID > a.MAIN_WORK_ID THEN a.MAIN_WORK_ID 
    END

现在需要在这个查询结果里新增一列，显示每个子工作的总成本，预期结果如下：

可以通过关联表+聚合函数计算总成本实现，以下是两种常用方法：

方法一：子查询预计算成本后关联 #

SELECT 
    a.WORK_ID, 
    a.MAIN_WORK_ID, 
    a.WORK_NAME,
    COALESCE(c.Total_Cost, 0) AS Total_Cost
FROM 
    PROJECTSWORKS a
LEFT JOIN (
    -- 先按WORK_ID聚合计算总成本
    SELECT 
        aw.WORK_ID,
        SUM(ac.ACTIVITY_COST) AS Total_Cost
    FROM ACTIVITIES aw
    JOIN ACTIVITY_COSTS ac 
        ON aw.ACTIVITY_ID = ac.ACTIVITY_ID 
        AND aw.PROJECT_ID = ac.PROJECT_ID
    WHERE aw.PROJECT_ID = 1
    GROUP BY aw.WORK_ID
) c ON a.WORK_ID = c.WORK_ID
WHERE 
    a.PROJECT_ID = 1
ORDER BY 
    CASE 
        WHEN a.WORK_ID = a.MAIN_WORK_ID THEN a.MAIN_WORK_ID
        WHEN a.WORK_ID < a.MAIN_WORK_ID THEN a.WORK_ID
        WHEN a.WORK_ID > a.MAIN_WORK_ID THEN a.MAIN_WORK_ID 
    END

方法二：直接关联三张表后聚合 #

SELECT 
    a.WORK_ID, 
    a.MAIN_WORK_ID, 
    a.WORK_NAME,
    SUM(ac.ACTIVITY_COST) AS Total_Cost
FROM 
    PROJECTSWORKS a
LEFT JOIN ACTIVITIES aw 
    ON a.WORK_ID = aw.WORK_ID 
    AND a.PROJECT_ID = aw.PROJECT_ID
LEFT JOIN ACTIVITY_COSTS ac 
    ON aw.ACTIVITY_ID = ac.ACTIVITY_ID 
    AND aw.PROJECT_ID = ac.PROJECT_ID
WHERE 
    a.PROJECT_ID = 1
GROUP BY 
    a.WORK_ID, a.MAIN_WORK_ID, a.WORK_NAME
ORDER BY 
    CASE 
        WHEN a.WORK_ID = a.MAIN_WORK_ID THEN a.MAIN_WORK_ID
        WHEN a.WORK_ID < a.MAIN_WORK_ID THEN a.WORK_ID
        WHEN a.WORK_ID > a.MAIN_WORK_ID THEN a.MAIN_WORK_ID 
    END

• 使用LEFT JOIN是为了确保即使某个工作没有对应活动或成本，也能在结果中显示（通过COALESCE将空值转为0）
• 两种方法都会保留你原有的排序规则，同时新增总成本列
• 计算逻辑：先关联活动表与成本表，按WORK_ID分组求和总成本，再关联回PROJECTSWORKS表

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