如何从关联表中汇总父部件的子部件总成本？

问题背景 #

你现在需要从主表（部件主清单）和子表（父部件对应的子部件集合）里，批量算出每个父部件所有子部件的总成本——也就是每个子部件的PricePer乘以Qty后的总和。子表靠ParentID关联父部件，ChildID指向主表的ID来获取子部件的单价，子表本身只存ID信息。

你的错误查询分析 #

你之前写的第一个查询为啥只返回0？问题出在关联和过滤条件的逻辑冲突：

SELECT SUM(PricePer * Qty) FROM sub inner join main on sub.ChildPID = main.ID where sub.ParentPID = main.ID

这里JOIN条件已经把主表和子表的子部件ID绑定了，后面的WHERE条件又要求子表的ParentPID等于主表的ID，相当于要求「子部件的ID等于父部件的ID」，这显然不符合你的数据逻辑，自然查不到有效数据，只能返回0。

而你手动指定sub.ParentPID = 1能得到正确结果，是因为直接锁定了单个父部件，但没法批量处理所有父部件。

正确的批量查询方案 #

这里给你两种常用的写法，按需选择：

1. 批量查询所有父部件的子部件总成本

这个写法会按父部件分组，返回每个父部件的ID、名称（可选）和对应的子部件总成本：

SELECT 
    sub.ParentID,
    main_parent.Name AS ParentName,
    SUM(main_child.PricePer * sub.Qty) AS TotalChildCost
FROM sub
-- 关联主表获取子部件的单价
JOIN main AS main_child ON sub.ChildID = main_child.ID
-- 关联主表获取父部件的名称（不需要的话可以去掉这个JOIN）
JOIN main AS main_parent ON sub.ParentID = main_parent.ID
-- 按父部件ID分组，计算每组的总和
GROUP BY sub.ParentID, main_parent.Name;

2. 将计算结果更新回主表的TotalCost字段

如果你的需求是把算出的总成本直接更新到主表的TotalCost列，可以用这个UPDATE语句：

UPDATE main
SET TotalCost = (
    SELECT SUM(m.PricePer * s.Qty)
    FROM sub s
    JOIN main m ON s.ChildID = m.ID
    WHERE s.ParentID = main.ID
)
-- 只更新有子部件的父部件，没有子部件的保持原数值
WHERE EXISTS (
    SELECT 1 FROM sub WHERE ParentID = main.ID
);

示例数据验证 #

用你提供的示例数据测试第一个查询：
主表数据：

子表数据：

运行查询后会得到：

结果正好是6*1 + 2*2 = 10，和主表里Part1的TotalCost一致，符合预期。

内容的提问来源于stack exchange，提问作者The man holds me down at 1 Rep