请教一个金蝶bom表展开的SQL语句

[hkent]

数据库结构 父原料 ,子原料,用料,投料用料(需要计算)

父原料 子原料 用料 投料用料
A10001 B1001 1 和用料一样
A10001 E1001 1 和用料一样
A10001 R9001 0.1 和用料一样
A10001 R9002 0.025 和用料一样
A10001 T9001 1 和用料一样
B1001 C1001 1.7 和用料一样
B1001 U1001 1 和用料一样
B1001 I9001 1 和用料一样
B1001 O001 1 和用料一样
C1001 OP0002 0.15 用料*1.7
C1001 OP0005 0.5 用料*1.7
C1001 OP0008 2.5 用料*1.7
C1001 OP0009 1.5 用料*1.7
C1001 OP0032 0.5 用料*1.7
C1001 OP0266 0.5 用料*1.7
C1001 OP0285 0.01 用料*1.7
C1001 OP0297 3 用料*1.7
C1001 OP0309 0.025 用料*1.7
C1001 OP0400 0.5 用料*1.7
C1001 OP0457 0.03 用料*1.7
C1001 OP0510 1 用料*1.7
C1001 OP0724 1 用料*1.7
C1001 OP0730 0.01 用料*1.7
C1001 OP0752 0.01 用料*1.7
C1001 OP0787 0.01 用料*1.7
C1001 OP0788 0.03 用料*1.7
C1001 OP0789 0.03 用料*1.7
C1001 OP0854 0.003 用料*1.7
C1001 OP0001 88.692 用料*1.7


想要得到的结果

父原料 子原料 用料 投料用料
A10001 B1001 1 1.0000000000
A10001 E1001 1 1.0000000000
A10001 R9001 0.1 0.1000000000
A10001 R9002 0.025 0.0250000000
A10001 T9001 1 1.0000000000
A10001_B1001 C1001 1.7 1.7000000000
A10001_B1001 U1001 1 1.0000000000
A10001_B1001 I9001 1 1.0000000000
A10001_B1001 O001 1 1.0000000000
A10001_B1001_C1001 OP0002 0.15 0.255
A10001_B1001_C1001 OP0005 0.5 0.85
A10001_B1001_C1001 OP0008 2.5 4.25
A10001_B1001_C1001 OP0009 1.5 2.55
A10001_B1001_C1001 OP0032 0.5 0.85
A10001_B1001_C1001 OP0266 0.5 0.85
A10001_B1001_C1001 OP0285 0.01 0.017
A10001_B1001_C1001 OP0297 3 5.1
A10001_B1001_C1001 OP0309 0.025 0.0425
A10001_B1001_C1001 OP0400 0.5 0.85
A10001_B1001_C1001 OP0457 0.03 0.051
A10001_B1001_C1001 OP0510 1 1.7
A10001_B1001_C1001 OP0724 1 1.7
A10001_B1001_C1001 OP0730 0.01 0.017
A10001_B1001_C1001 OP0752 0.01 0.017
A10001_B1001_C1001 OP0787 0.01 0.017
A10001_B1001_C1001 OP0788 0.03 0.051
A10001_B1001_C1001 OP0789 0.03 0.051
A10001_B1001_C1001 OP0854 0.003 0.0051
A10001_B1001_C1001 OP0001 88.692 150.7764

A10001_B1001_C1001 产品下的投料用量 是 本身自己的用量除以上一级用量的数量(1.7)

我现在的语句是 ,用游标循环,插入表的方法
先用游标循环出父原料代码,循环到A10001_B1001这一级,然后嵌套一个游标循环A10001_B1001下面的子代码,计算投料用量.
但是好像效率不行 ,运行一次要半个小时.

[xxfvba]

--2005以上版本,假设表为BOM,不知道理解的对错 With CTE as (select 父原料,子原料,用料,用料 as 投料用料 from BOM where not exists (select * from BOM a where a.子原料=BOM.父原料) union all select b.父原料,a.子原料,a.用料,b.投料用料*a.用料 from BOM a,CTE b where a.父原料=b.子原料) select * from CTE

[---涛声依旧---]

楼主在金蝶上班？

[hkent]

谢谢楼上2位 但不是我想要的那种.

引用 4 楼 rockyljt 的回复:

楼主在金蝶上班？

不是,单位正好用金蝶的ERP.

[hkent]

终于好上传图片了,我要这样的格式,这个只是其中的一个产品.
BOM里面大概有3000个产品要输出右边那张格式的表.

[duanzhi1984]

使用公用表达式CTE 或者循环

[hkent]

右边的 投放用量我计算错了 公式就是取C1001的用量1.7 ,然后用1.7去乘以那些组装成C1001那些配件(op开头的的标准用量.得到用于计算成本的投放用量.

[leeya66]

要运行半小时等于没有。记住：无论如何不能用游标。方法是：加一个level字段，然后用cte的递归查询。网上能找到一个案例，很具体

[还在加载中灬]

这个数据没有维护出一个PATH，很不方便的 除了游标，还有CTE或者直接动态语句查询 但都不会很快

[舞台中央的我]

业务知识没看明白

[Tiger_Zhao]

加一个 level 字段作为辅助，就可以批量更新

DECLARE @level int

SET @level = 1

UPDATE bom
   SET 投料用料 = 用料,
       level = @level
 WHERE NOT EXISTS (SELECT *
                     FROM bom p
                    WHERE p.子原料 = bom.父原料)

WHILE @@ROWCOUNT > 0
BEGIN
    SET @level = @level + 1

    UPDATE bom
       SET c.投料用料 = c.用料 * p.投料用料,
           c.level = @level
     FROM bom c, bom p
     WHERE p.level = @level - 1
       AND p.子原料 = c.父原料
END

[hkent]

引用 9 楼 rrxxjj1234 的回复:

要运行半小时等于没有。记住：无论如何不能用游标。方法是：加一个level字段，然后用cte的递归查询。网上能找到一个案例，很具体

具体叫什么案列 有关键字么.

引用 10 楼 BIBIHAHA 的回复:

业务知识没看明白

恩,还在研究当中.

引用 11 楼 Tiger_Zhao 的回复:

你按我#2的思路做，按层循环最适合批量了，保证是最快的。 用个临时表，加level字段就没影响了；按照输出格式，再加个父路径和子路径的合并运算。输出时把父路径作为父原料列。

好的.我去试试看.

[Tiger_Zhao]

你按我#2的思路做，按层循环最适合批量了，保证是最快的。
用个临时表，加level字段就没影响了；按照输出格式，再加个父路径和子路径的合并运算。输出时把父路径作为父原料列。

[AnthonyGe]

WITH union_result as ( SELECT t_item.FFullNumber Id, NULL ParentID, FQty FROM dbo.ICBOM INNER JOIN dbo.t_Item ON t_item.FItemID = ICBOM.FItemID UNION ALL SELECT ci.FFullNumber Id, pi.FFullNumber ParentId, c.FQty * b.FQty FROM dbo.ICBOMChild c INNER JOIN icbom b ON b.FInterID = c.FInterID INNER JOIN t_Item ci ON ci.FItemID = c.FItemID INNER JOIN t_item pi ON pi.FItemID = b.FItemID ), bom as ( SELECT Id, ParentID, 0 AS level, CAST(Id AS VarChar(Max)) Path, Id TopId, FQty FROM union_result WHERE ParentID IS NULL UNION ALL SELECT c.Id, c.ParentId, b.level + 1, CAST(b.Path AS VarChar(Max))+' ; '+c.Id, b.TopId AS Path, c.FQty * b.FQty FROM union_result c INNER JOIN bom b ON b.Id = c.ParentID ), filter_parent as ( select * from bom b1 where NOT EXISTS (SELECT * FROM bom b2 WHERE b2.ParentId = b1.Id) ), group_result as ( select Id, TopId, sum(FQty) FQty from filter_parent group by id, TopId ) select Id 子项物料代码, c.FName 物料名称, c.FModel 规格型号, case c.FErpClsID when 1 then '外购' when 2 then '自制' when 3 then '委外加工' when 5 then '虚拟件' else '' end 物料属性, u.FName 单位, r.FQty 单位用量, p.FTopID 物料代码,p.FName 物料名称, p.FModel 规格型号 from group_result r inner join t_ICItem c on c.FNumber = r.Id inner join t_ICItem p on p.FNumber = r.TopId inner join t_Item u on u.FitemId = c.FUnitID