SQLite中主/子WBS分类成本求和查询问题及修正需求

表结构与数据 #

PROJWBS表（项目主/子工作） #

TASK表（工作任务） #

TASKRSRC表（任务目标成本） #

原查询与问题 #

原查询尝试按层级排序并计算各工作成本，但主工作（WBS_ID=1）的成本始终显示为0，仅子工作成本计算正确。原SQL如下：

SELECT 
    PROJWBS.Wbs_id, PROJWBS.Parent_Wbs_id, PROJWBS.Wbs_name,  
    COALESCE(subquery.Total_Cost, 0) AS Total_Cost 
FROM 
    PROJWBS 
LEFT JOIN 
    (SELECT 
         TASK.Wbs_id, SUM(TASKRSRC.Target_Cost) AS Total_Cost 
     FROM 
         TASK 
     JOIN 
         TASKRSRC ON TASK.Task_id = TASKRSRC.Task_id  
     GROUP BY 
         TASK.Wbs_id) AS subquery ON PROJWBS.Wbs_id = subquery.Wbs_id  
WHERE 
    PROJ_ID = 1
GROUP BY 
    PROJWBS.Wbs_id, PROJWBS.Parent_Wbs_id, PROJWBS.Wbs_name    
ORDER BY 
    CASE  
        WHEN PROJWBS.Wbs_id = PROJWBS.PARENT_WBS_ID THEN PROJWBS.PARENT_WBS_ID  
        WHEN PROJWBS.Wbs_id < PROJWBS.PARENT_WBS_ID THEN PROJWBS.WBS_ID   
        WHEN PROJWBS.Wbs_id > PROJWBS.PARENT_WBS_ID THEN PROJWBS.PARENT_WBS_ID 
    END;

问题原因：原查询仅关联了直接绑定任务的WBS节点，主工作没有直接关联任务，因此无法自动汇总子节点的成本总和。

期望结果 #

需要返回包含主工作总成本（所有子工作成本之和）的结果，同时保留原排序逻辑：

解决方案 #

使用SQLite的递归CTE（公共表表达式）遍历WBS层级，累加所有子节点的成本。具体SQL如下：

WITH RECURSIVE wbs_cost AS (
    -- 基础步骤：计算每个WBS的直接任务成本
    SELECT 
        p.WBS_ID,
        p.PARENT_WBS_ID,
        p.WBS_NAME,
        COALESCE(tc.Total_Cost, 0) AS Direct_Cost
    FROM PROJWBS p
    LEFT JOIN (
        SELECT 
            t.WBS_ID,
            SUM(tr.TARGET_COST) AS Total_Cost
        FROM TASK t
        JOIN TASKRSRC tr ON t.TASK_ID = tr.TASK_ID
        WHERE t.PROJ_ID = 1
        GROUP BY t.WBS_ID
    ) tc ON p.WBS_ID = tc.WBS_ID
    WHERE p.PROJ_ID = 1

    UNION ALL

    -- 递归步骤：向上累加子节点的成本到父节点
    SELECT 
        parent.WBS_ID,
        parent.PARENT_WBS_ID,
        parent.WBS_NAME,
        parent.Direct_Cost + child.Direct_Cost AS Direct_Cost
    FROM wbs_cost parent
    JOIN wbs_cost child ON parent.WBS_ID = child.PARENT_WBS_ID
),
-- 计算每个WBS的最终总成本（去重，取每个节点的最大累加值）
final_cost AS (
    SELECT 
        WBS_ID,
        WBS_NAME,
        MAX(Direct_Cost) AS Total_Cost
    FROM wbs_cost
    GROUP BY WBS_ID, WBS_NAME
)
-- 关联原表，保留要求的排序逻辑
SELECT 
    fc.WBS_ID,
    fc.WBS_NAME,
    fc.Total_Cost
FROM final_cost fc
JOIN PROJWBS p ON fc.WBS_ID = p.WBS_ID
WHERE p.PROJ_ID = 1
ORDER BY 
    CASE  
        WHEN p.Wbs_id = p.PARENT_WBS_ID THEN p.PARENT_WBS_ID  
        WHEN p.Wbs_id < p.PARENT_WBS_ID THEN p.WBS_ID   
        WHEN p.Wbs_id > p.PARENT_WBS_ID THEN p.PARENT_WBS_ID 
    END;

逻辑说明 #

1. 基础CTE（wbs_cost）：先计算每个WBS节点的直接任务成本，子工作会得到对应任务的总成本，主工作直接成本为0。
2. 递归步骤：将子节点的成本累加到其父节点的成本中，实现层级求和。
3. final_cost：由于递归会生成多条同一节点的记录，取每个节点的最大成本值（即包含所有子节点的总和）。
4. 最终查询：关联原PROJWBS表，应用原排序逻辑，得到期望结果。

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