双表单层级结构下多级层级计算的更新/插入实现求助

测试数据SQL #

Create Table #BOM -- 父表
(
Id Int Primary Key Identity(1,1),
ProductName Varchar(500),
Revision  Varchar(500),
Factory  Varchar(500)
)

Create Table #BOMDetails -- 子表
(
Id Int Primary Key Identity(1,1),
BomId Int Foreign Key references #BOM(Id),
ComponentName Varchar(500),
Revision  Varchar(500),
BomQty  Decimal(10,2),
ActualQty Decimal(10,2)
)

Insert Into #BOM(ProductName , Revision , Factory) Values('AA','A','JPD')
Insert Into #BOM(ProductName , Revision , Factory) Values('BA','A','JPD')
Insert Into #BOM(ProductName , Revision , Factory) Values('AB','A','JPD')
Insert Into #BOM(ProductName , Revision , Factory) Values('ABC','E','JPD')


Insert Into #BOMDetails(BomId ,ComponentName , Revision , BomQty) Values(1,'AB','A',1.20)
Insert Into #BOMDetails(BomId ,ComponentName , Revision , BomQty) Values(3,'ABC','E',1.50)
Insert Into #BOMDetails(BomId ,ComponentName , Revision , BomQty) Values(2,'BB','C',2)
Insert Into #BOMDetails(BomId ,ComponentName , Revision , BomQty) Values(4,'ABCD','C',2)

Select * from #BOM
Select * from #BOMDetails

BOM层级关系 #

需求说明 #

现有#BOM（父表）和#BOMDetails（子表）两张单层级结构表，已插入测试数据。需要更新#BOMDetails中的ActualQty字段，使其等于该组件所有父级节点BomQty的乘积（根节点默认BomQty为1）。例如组件ABCD的ActualQty计算为：1（AA的默认值）*1.2（AB的BomQty）*1.5（ABC的BomQty）*2（ABCD的BomQty）=3.60。由于数据量可达数十万级，遍历表的方式效率低下，需要高效的Update或Insert查询语句。

高效解决方案 #

使用SQL Server的递归CTE（公共表表达式）来遍历BOM层级，一次性计算所有组件的累计乘积，再批量更新ActualQty字段，避免逐行遍历的性能损耗。

;WITH BOMHierarchy AS (
    -- 锚点成员：定位BOM根节点（未被其他组件引用的产品）
    SELECT 
        b.Id AS ParentId,
        b.ProductName AS ComponentName,
        b.Revision,
        CAST(1.00 AS DECIMAL(10,2)) AS CumulativeQty -- 根节点默认乘积为1
    FROM #BOM b
    WHERE NOT EXISTS (
        SELECT 1 FROM #BOMDetails bd 
        WHERE bd.ComponentName = b.ProductName AND bd.Revision = b.Revision
    )

    UNION ALL

    -- 递归成员：逐层计算子组件的累计乘积
    SELECT 
        bd.BomId AS ParentId,
        bd.ComponentName,
        bd.Revision,
        CAST(bh.CumulativeQty * bd.BomQty AS DECIMAL(10,2)) AS CumulativeQty
    FROM #BOMDetails bd
    INNER JOIN BOMHierarchy bh 
        ON bd.ComponentName = bh.ComponentName AND bd.Revision = bh.Revision
)
-- 批量更新ActualQty字段
UPDATE bd
SET bd.ActualQty = bh.CumulativeQty
FROM #BOMDetails bd
INNER JOIN BOMHierarchy bh 
    ON bd.ComponentName = bh.ComponentName AND bd.Revision = bh.Revision;

-- 验证更新结果
SELECT * FROM #BOMDetails;

方案说明 #

1. 锚点成员：筛选出所有BOM的根节点（即没有作为子组件出现在#BOMDetails中的产品），默认其累计乘积为1；
2. 递归成员：通过JOIN关联父节点的累计乘积，与当前组件的BomQty相乘，得到当前组件的最终ActualQty；
3. 批量更新：通过集合操作一次性完成所有组件的ActualQty更新，相比逐行遍历效率提升显著，适合数十万级数据量；
4. 版本匹配：使用ComponentName+Revision作为唯一标识，避免同一组件不同版本的计算混淆。

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