再请教 MSSQL2005 深度计算多版本bom单价
MSSQL2005 深度计算多版本bom单价 ---- 两种计算方法展开
表关系：goodsbom.billid=goodsbomdetail.billid 相同时表示同一层bom
主表goodsbom
单据ID，物料母件ID，版本号，母件数量
billid,materialid,bomno,quantity
1001 , A , AA , 1
1002 , B , BB , 1
1003 , F , FF , 1
1004 , A , AK , 1
从表goodsbomdetail
单据ID，明细序号， 物料子件ID,子件数量 ，子件版本号
billid,itemno,elemgid,quantity,bomno
1001 , 1 , B , 1 , BB
1001 , 2 , C , 1 , ' '
1001 , 3 , D , 1 , ' '
1002 , 1 , E , 1 , ' '
1002 , 2 , F , 2 , FF
1003 , 1 , G , 1 , ' '
1003 , 2 , H , 1 , ' '
1004 , 1 , J , 1 , ' '
1004 , 2 , K , 1 , ' '
物料单价表materialprice
materailid , price
A , 2
B , 1
C , 1
D , 1
E , 1
F , 1
G , 1
F , 1
J , 1
K , 1
要求结果如下： 以查询物料A为例， 如果查询物料B或物料F同理。 两种计算方法展开
1/计算最下层物料 (从最下层显示所需材料)
物料母件ID，母件版本号，母件数量 ，子件ID ， 子件数量 ,子件序号，子件单价，子件金额
materialid ,bomno ,quantity ,elemgid,goodsbomdetailquantity,goodsbomdetailbomno ,materialprice ,materialamt
A , AA , 1 , C , 1 , ' ' , 1 , 1
A , AA , 1 , D , 1 , ' ' , 1 , 1
A , AA , 1 , E , 1 , ' ' , 1 , 1
A , AA , 1 , G , 2 , ' ' , 1 , 2 ----请注意 这里需要子件G为2个
A , AA , 1 , H , 2 , ' ' , 1 , 2 ----请注意 这里需要子件H为2个
A , AK , 1 , J , 1 , ' ' , 1 , 1
A , AK , 1 , K , 1 , ' ' , 1 , 1
2/计算下一层物料 (只计算查询层所在材料或半成品)
materialid ,bomno ,quantity ,elemgid,goodsbomdetailquantity,goodsbomdetailbomno ,materialprice ,materialamt
A , AA , 1 , B , 1 , BB , 1 , 5 ---注意 这里的单价为B下级材料求和
A , AA , 1 , C , 1 , ' ' , 1 , 1
A , AA , 1 , D , 1 , ' ' , 1 , 1
A , AK , 1 , J , 1 , ' ' , 1 , 1
A , AK , 1 , K , 1 , ' ' , 1 , 1
感谢各位的帮忙!
[解决办法]
-- #goodsbomif object_id('tempdb.dbo.#goodsbom') is not null drop table #goodsbomcreate table #goodsbom(billid int, materialid varchar(8), bomno varchar(8), quantity int)insert into #goodsbomselect 1001, 'A', 'AA', 1 union allselect 1002, 'B', 'BB', 1 union allselect 1003, 'F', 'FF', 1 union allselect 1004, 'A', 'AK', 1-- #goodsbomdetailif object_id('tempdb.dbo.#goodsbomdetail') is not null drop table #goodsbomdetailcreate table #goodsbomdetail(billid int, itemno int, elemgid varchar(8), quantity int, bomno varchar(8))insert into #goodsbomdetailselect 1001, 1, 'B', 1, 'BB' union allselect 1001, 2, 'C', 1, '' union allselect 1001, 3, 'D', 1, '' union allselect 1002, 1, 'E', 1, '' union allselect 1002, 2, 'F', 2, 'FF' union allselect 1003, 1, 'G', 1, '' union allselect 1003, 2, 'H', 1, '' union allselect 1004, 1, 'J', 1, '' union allselect 1004, 2, 'K', 1, ''-- #materialpriceif object_id('tempdb.dbo.#materialprice') is not null drop table #materialpricecreate table #materialprice(materailid varchar(8), price int)insert into #materialpriceselect 'A', 2 union allselect 'B', 1 union allselect 'C', 1 union allselect 'D', 1 union allselect 'E', 1 union allselect 'F', 1 union allselect 'G', 1 union allselect 'H', 1 union allselect 'J', 1 union allselect 'K', 1-- 1;with cte(mid,mbom,mqty,id,qty,bom) as( select a.materialid, a.bomno, a.quantity, b.elemgid, a.quantity*b.quantity, b.bomno from #goodsbom a, #goodsbomdetail b where a.billid=b.billid and a.materialid='A' union all select c.mid, c.mbom, c.mqty, b.elemgid, a.quantity*b.quantity*c.qty, b.bomno from #goodsbom a, #goodsbomdetail b, cte c where a.billid=b.billid and a.bomno=c.bom)select a.*, b.price, amt=a.qty*b.price from cte a, #materialprice b where a.id=b.materailid and a.bom=''/*mid mbom mqty id qty bom price amt-------- -------- ----------- -------- ----------- -------- ----------- -----------A AA 1 C 1 1 1A AA 1 D 1 1 1A AA 1 E 1 1 1A AA 1 G 2 1 2A AA 1 H 2 1 2A AK 1 J 1 1 1A AK 1 K 1 1 1*/-- 2;with cte(mid,mbom,mqty,id,qty,bom,id2,qty2,bom2) as( select a.materialid, a.bomno, a.quantity, b.elemgid, a.quantity*b.quantity, b.bomno, b.elemgid, a.quantity*b.quantity, b.bomno from #goodsbom a, #goodsbomdetail b where a.billid=b.billid and a.materialid='A' union all select c.mid, c.mbom, c.mqty, c.id, c.qty, c.bom, b.elemgid, a.quantity*b.quantity*c.qty2, b.bomno from #goodsbom a, #goodsbomdetail b, cte c where a.billid=b.billid and a.bomno=c.bom2)select mid, mbom, mqty, id, qty, bom, max(b.price)price, sum(qty2*c.price)amt from cte a, #materialprice b, #materialprice c where a.bom2='' and a.id=b.materailid and a.id2=c.materailid group by mid, mbom, mqty, id, qty, bom/*mid mbom mqty id qty bom price amt-------- -------- ----------- -------- ----------- -------- ----------- -----------A AA 1 B 1 BB 1 5A AA 1 C 1 1 1A AA 1 D 1 1 1A AK 1 J 1 1 1A AK 1 K 1 1 1*/
[解决办法]
-- function f2create function f2(@mid varchar(8))returns @tb table(mid varchar(8),mbom varchar(8),mqty int,id varchar(8),qty int,bom varchar(8),price int,amt int)asbegindeclare @level intset @level = 0declare @nest table (lev int/*层级深度递归必须*/, mid varchar(8),mbom varchar(8),mqty int,id varchar(8),qty int,bom varchar(8),id2 varchar(8),qty2 int,bom2 varchar(8))insert into @nest select @level, a.materialid, a.bomno, a.quantity, b.elemgid, a.quantity*b.quantity, b.bomno, b.elemgid, a.quantity*b.quantity, b.bomno from goodsbom a, goodsbomdetail b where a.billid=b.billid and a.materialid=@midwhile @@rowcount>0begin set @level=@level+1 insert into @nest select @level, c.mid, c.mbom, c.mqty, c.id, c.qty, c.bom, b.elemgid, a.quantity*b.quantity*c.qty2, b.bomno from goodsbom a, goodsbomdetail b, @nest c where c.lev=@level-1 and a.billid=b.billid and a.bomno=c.bom2endinsert into @tb select mid, mbom, mqty, id, qty, bom, max(b.price)price, sum(qty2*c.price)amt from @nest a, materialprice b, materialprice c where a.bom2='' and a.id=b.materailid and a.id2=c.materailid group by mid, mbom, mqty, id, qty, bomreturnendgo-- test f2select * from f2('A')