BOM穿透子项查询问题
原始表
父项ID父项描述子项ID子项描述用量层
00-001成品料号01-001半成品1 10
01-001半成品1 05-001原材料1 0.51
01-001半成品1 01-002半成品2 21
01-002半成品2 06-001原材料2 12
01-002半成品2 06-002原材料3 32

结果表
父项ID父项描述子项ID子项描述用量
00-001成品料号05-001原材料1 0.5
00-001成品料号06-001原材料2 2
00-001成品料号06-002原材料3 6

如何穿透半成品直接取出原材料的用量
[解决办法]

;WITH CTE
AS
(
SELECT 父项ID,父项描述,子项ID,子项描述,CAST( 用量 AS NUMERIC(8,2)) AS 用量 ,层
FROM dbo.TB
WHERE 层= 0
UNION ALL
SELECT C.父项ID,C.父项描述,B.子项ID,B.子项描述,CAST(B.用量*C.用量 AS NUMERIC(8,2)) AS 用量,B.层
FROM CTE AS C
INNER JOIN TB B ON C.子项ID = B.父项ID
)

SELECT *
FROM CTE
WHERE 子项描述 LIKE '原材料%'

/*
00-001成品料号05-001原材料10.501
00-001成品料号06-001原材料22.002
00-001成品料号06-002原材料36.002*/

[解决办法]
create table tb(fID varchar(10),finfo nvarchar(10),sID varchar(10),sinfo nvarchar(10),num decimal(10,2),l int)
insert into tb select '00-001','成品料号','01-001','半成品1',1,0
insert into tb select '01-001','半成品1','05-001','原材料1',0.5,1
insert into tb select '01-001','半成品1','01-002','半成品2',2,1
insert into tb select '01-002','半成品2','06-001','原材料2',1,2
insert into tb select '01-002','半成品2','06-002','原材料3',3,2
go
;with cte as(
select * from tb a where not exists(select 1 from tb where fid=a.sid)
union all
select a.fid,a.finfo,b.sid,b.sinfo,convert(decimal(10,2),a.num*b.num),a.l from tb a inner join cte b on a.sid=b.fid
)select * from cte where l=0
/*
fID finfo sID sinfo num l
---------- ---------- ---------- ---------- --------------------------------------- -----------
00-001 成品料号 06-002 原材料3 6.00 0
00-001 成品料号 06-001 原材料2 2.00 0
00-001 成品料号 05-001 原材料1 0.50 0

(3 行受影响)

*/
go
drop table tb