22,206
社区成员
发帖
与我相关
我的任务
分享
-- function f2
create 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)
as
begin
declare @level int
set @level = 0
declare @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=@mid
while @@rowcount>0
begin
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.bom2
end
insert 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, bom
return
end
go
-- test f2
select * from f2('A')
-- 2:2000递归
declare @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))
declare @level int
set @level = 0 -- lev初始化
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='A'
while @@rowcount>0
begin
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.bom2
end
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, bom
create view view1 as -- 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=''
go
create function function2(@mid varchar(100)) -- 2/
returns table return
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=@mid
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
go
-- test
select * from view1 where mid='A'
select * from function2('A')
-- #goodsbom
if object_id('tempdb.dbo.#goodsbom') is not null drop table #goodsbom
create table #goodsbom(billid int, materialid varchar(8), bomno varchar(8), quantity int)
insert into #goodsbom
select 1001, 'A', 'AA', 1 union all
select 1002, 'B', 'BB', 1 union all
select 1003, 'F', 'FF', 1 union all
select 1004, 'A', 'AK', 1
-- #goodsbomdetail
if object_id('tempdb.dbo.#goodsbomdetail') is not null drop table #goodsbomdetail
create table #goodsbomdetail(billid int, itemno int, elemgid varchar(8), quantity int, bomno varchar(8))
insert into #goodsbomdetail
select 1001, 1, 'B', 1, 'BB' union all
select 1001, 2, 'C', 1, '' union all
select 1001, 3, 'D', 1, '' union all
select 1002, 1, 'E', 1, '' union all
select 1002, 2, 'F', 2, 'FF' union all
select 1003, 1, 'G', 1, '' union all
select 1003, 2, 'H', 1, '' union all
select 1004, 1, 'J', 1, '' union all
select 1004, 2, 'K', 1, ''
-- #materialprice
if object_id('tempdb.dbo.#materialprice') is not null drop table #materialprice
create table #materialprice(materailid varchar(8), price int)
insert into #materialprice
select 'A', 2 union all
select 'B', 1 union all
select 'C', 1 union all
select 'D', 1 union all
select 'E', 1 union all
select 'F', 1 union all
select 'G', 1 union all
select 'H', 1 union all
select 'J', 1 union all
select '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 1
A AA 1 D 1 1 1
A AA 1 E 1 1 1
A AA 1 G 2 1 2
A AA 1 H 2 1 2
A AK 1 J 1 1 1
A 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 5
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
*/