TABLE1 TABLE2
NO1 FATHER NO2 MONEY
1 0 3 30
2 0 4 40
3 1 5 50
4 1
5 2现在拿NO1=1 查,想得到他的孩子以下的MONEY的和.比如 4和3是1 的孩子,5是2的孩子, 查的结果=30+40+50=120如何实现.有劳各位了,小妹在这里先谢谢了!
NO1 FATHER NO2 MONEY
1 0 3 30
2 0 4 40
3 1 5 50
4 1
5 2现在拿NO1=1 查,想得到他的孩子以下的MONEY的和.比如 4和3是1 的孩子,5是2的孩子, 查的结果=30+40+50=120如何实现.有劳各位了,小妹在这里先谢谢了!
NO1 FATHER NO2 MONEY
1 0 3 30
2 0 4 40
3 1 5 50
4 1
5 2
from table1 a
inner Join table2 as b on a.NO1=b.NO2
where a.FATHER=1
/*
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,2
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
-----------
70*/
--删除测试数据
drop function f_getChild
drop table BOM,table2
这样好象只能查出 4和3的MONEY 如果5是3的孩子的话,也应该加上5的MINEY ..
这样好象只能查出 4和3的MONEY 如果5是3的孩子的话,也应该加上5的MINEY ..
---
看看你的问题,
select sum(t2.money) from table2 t2,(select no1 from table1 where father=1) t1 where t1.no1=t2.no2
declare @t2 table(no2 int,money int)
insert into @t1 select 1,0
union all select 2,0
union all select 3,1
union all select 4,1
union all select 5,2
insert into @t2 select 3,30
union all select 4,40
union all select 5,50
-----------
select MONEY和=sum(t2.money) from @t2 t2,(select no1 from @t1 where father=1) t1 where t1.no1=t2.no2
/*
MONEY和
-----------
70(所影响的行数为 1 行)*/