sql代码,代码精简,bom表要算到多层表的格式是这样的parentchildnumdjmAB11AC21AD5

Question

sql代码,代码精简,bom表要算到多层
表的格式是这样的
parent child num djm
A B 1 1
A C 2 1
A D 5 1
B E 10 2
B F 13 2
C G 7 2
C H 8 2
C I 3 2
D W 3 2
D Q 4 2
W VV 12 3
要算出最底层物料的数量
select * into t1 from B_bom where djm=1;
select * into t2 from B_bom where djm=2;
select * into t3 from B_bom where djm=3;
select
case
when t3.child IS null then t2.child
else t3.child
end "物料",
t1.num* t2.num*isnull( t3.num,1) as "数量"
from t1 left join t2 on t1.child= t2.parent left join t3 on t2.child= t3.parent
已有代码能够算出来 但是要是层数太多这代码就太复杂,来为大神来精简下,能够算到多层.

Answer 1

With TAs(Select * From T_BOM Where djm=1Union AllSelect A.parent,A.child,T.num*A.num,A.djm From T_BOM A Inner Join T on A.parent=T.child)Select child,num From TWhere Not Exists( Select parent From T_...