--生成测试数据
/*
TABLE1 TABLE2
NO1 FATHER NO2 MONEY
1 0 3 30
2 0 4 40
3 1 5 50
4 1
5 2
*/create table BOM(ID INT,PID INT)
insert into BOM select 1,0
insert into BOM select 2,0
insert into BOM select 3,1
insert into BOM select 4,1
insert into BOM select 5,3
create table table2(no2 int ,money2 int)
insert table2 select 3,30
insert table2 select 4,40
insert table2 select 5,50
go--创建用户定义函数用于取每个父节点下子节点的采购配置信息
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go--执行查询select sum(money2) as [money]
from table2 where no2 in(select ID from dbo.f_getChild(1))
--输出结果/*
money
-----------
120*/
--删除测试数据
drop function f_getChild
drop table BOM,table2
/*
TABLE1 TABLE2
NO1 FATHER NO2 MONEY
1 0 3 30
2 0 4 40
3 1 5 50
4 1
5 2
*/create table BOM(ID INT,PID INT)
insert into BOM select 1,0
insert into BOM select 2,0
insert into BOM select 3,1
insert into BOM select 4,1
insert into BOM select 5,3
create table table2(no2 int ,money2 int)
insert table2 select 3,30
insert table2 select 4,40
insert table2 select 5,50
go--创建用户定义函数用于取每个父节点下子节点的采购配置信息
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go--执行查询select sum(money2) as [money]
from table2 where no2 in(select ID from dbo.f_getChild(1))
--输出结果/*
money
-----------
120*/
--删除测试数据
drop function f_getChild
drop table BOM,table2
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货