create table test (id int primary key,name char(6),sj_id int REFERENCES test(id)) --测试数据 insert test values(1,'A',1) insert test values(2,'B',1) insert test values(10,'C',2) insert test values(11,'D',10) insert test values(12,'E',11) insert test values(13,'F',10) insert test values(14,'G',12)--实现 declare @id int declare @cs int set @id = 10 Create table #tmp_test_tree(id int not null,CS int not null) Select @cs = 1 Insert Into #tmp_test_tree(id,CS) Values(@id,@cs) While (@@ROWCOUNT >= 1) Begin select @cs = @cs + 1 Insert #tmp_test_tree(id,CS) Select a.id,@cs From test a,#tmp_test_tree b Where a.sj_id = b.id and b.CS = @cs - 1 and a.id <> a.sj_id End select a.id,b.name from #tmp_test_tree a join test b on a.id = b.id where cs > 1drop table #tmp_test_tree,test id name ----------- ------ 11 D 13 F 12 E 14 G (所影响的行数为 4 行)------------------------------------------------------------------------------------ 部门表: Dep_ID Dep_Name Dep_Father Dep_Level 1 总部 0 1 2 部门1 1 2 3 部门1-1 2 3 4 部门2 1 2 5 部门2-1 4 3 6 部门2-2 4 3 7 部门2-1-1 5 4 业绩表 Dep_ID Dep_Gross 1 2500 3 4500 4 200 5 600 6 360 7 650 求部门2的总业绩(包括:部门2、部门2-1、部门2-2、部门2-1-1) declare @部门表 table(Dep_ID int,Dep_Name varchar(10),Dep_Father int,Dep_Level int) insert into @部门表 select 1,'总部',0,1 union all select 2,'部门1',1,2 union all select 3,'部门1-1',2,3 union all select 4,'部门2',1,2 union all select 5,'部门2-1',4,3 union all select 6,'部门2-2',4,3 union all select 7,'部门2-1-1',5,4declare @业绩表 table(Dep_ID int,Dep_Gross int) insert into @业绩表 select 1,2500 union all select 3,4500 union all select 4,200 union all select 5,600 union all select 6,360 union all select 7,650 --统计处理 declare @t table(Dep_ID int,idm varchar(8000)) declare @level int set @level=1 insert into @t select Dep_ID,','+cast(Dep_ID as varchar)+',' from @部门表 where Dep_Level=1 while @@rowcount>0 begin set @level=@level+1 insert into @t select a.Dep_ID,b.idm+cast(a.Dep_ID as varchar)+',' from @部门表 a join @t b on a.Dep_Father=b.Dep_ID where a.Dep_Level=@level end--统计业绩 select a.Dep_ID,a.Dep_Name,业绩=sum(b.Dep_Gross) from ( select a.Dep_ID,a.Dep_Name,idm='%'+b.idm +'%' from @部门表 a join @t b on a.Dep_ID=b.Dep_ID )a join( select a.*,b.idm from @业绩表 a join @t b on a.Dep_ID=b.Dep_ID ) b on b.idm like a.idm group by a.Dep_ID,a.Dep_Name/*--测试结果Dep_ID Dep_Name 业绩 ----------- ---------- ----------- 1 总部 8810 2 部门1 4500 3 部门1-1 4500 4 部门2 1810 5 部门2-1 1250 6 部门2-2 360 7 部门2-1-1 650(所影响的行数为 7 行) --*/
--测试数据
insert test values(1,'A',1)
insert test values(2,'B',1)
insert test values(10,'C',2)
insert test values(11,'D',10)
insert test values(12,'E',11)
insert test values(13,'F',10)
insert test values(14,'G',12)--实现
declare @id int
declare @cs int
set @id = 10
Create table #tmp_test_tree(id int not null,CS int not null)
Select @cs = 1
Insert Into #tmp_test_tree(id,CS) Values(@id,@cs)
While (@@ROWCOUNT >= 1)
Begin
select @cs = @cs + 1
Insert #tmp_test_tree(id,CS)
Select a.id,@cs From test a,#tmp_test_tree b
Where a.sj_id = b.id and b.CS = @cs - 1 and a.id <> a.sj_id
End
select a.id,b.name from #tmp_test_tree a join test b on a.id = b.id where cs > 1drop table #tmp_test_tree,test
id name
----------- ------
11 D
13 F
12 E
14 G (所影响的行数为 4 行)------------------------------------------------------------------------------------
部门表:
Dep_ID Dep_Name Dep_Father Dep_Level
1 总部 0 1
2 部门1 1 2
3 部门1-1 2 3
4 部门2 1 2
5 部门2-1 4 3
6 部门2-2 4 3
7 部门2-1-1 5 4
业绩表
Dep_ID Dep_Gross
1 2500
3 4500
4 200
5 600
6 360
7 650 求部门2的总业绩(包括:部门2、部门2-1、部门2-2、部门2-1-1) declare @部门表 table(Dep_ID int,Dep_Name varchar(10),Dep_Father int,Dep_Level int)
insert into @部门表
select 1,'总部',0,1
union all select 2,'部门1',1,2
union all select 3,'部门1-1',2,3
union all select 4,'部门2',1,2
union all select 5,'部门2-1',4,3
union all select 6,'部门2-2',4,3
union all select 7,'部门2-1-1',5,4declare @业绩表 table(Dep_ID int,Dep_Gross int)
insert into @业绩表
select 1,2500
union all select 3,4500
union all select 4,200
union all select 5,600
union all select 6,360
union all select 7,650 --统计处理
declare @t table(Dep_ID int,idm varchar(8000))
declare @level int
set @level=1
insert into @t select Dep_ID,','+cast(Dep_ID as varchar)+','
from @部门表 where Dep_Level=1
while @@rowcount>0
begin
set @level=@level+1
insert into @t select a.Dep_ID,b.idm+cast(a.Dep_ID as varchar)+','
from @部门表 a join @t b on a.Dep_Father=b.Dep_ID
where a.Dep_Level=@level
end--统计业绩
select a.Dep_ID,a.Dep_Name,业绩=sum(b.Dep_Gross)
from (
select a.Dep_ID,a.Dep_Name,idm='%'+b.idm +'%'
from @部门表 a join @t b on a.Dep_ID=b.Dep_ID
)a join(
select a.*,b.idm
from @业绩表 a join @t b on a.Dep_ID=b.Dep_ID
) b on b.idm like a.idm
group by a.Dep_ID,a.Dep_Name/*--测试结果Dep_ID Dep_Name 业绩
----------- ---------- -----------
1 总部 8810
2 部门1 4500
3 部门1-1 4500
4 部门2 1810
5 部门2-1 1250
6 部门2-2 360
7 部门2-1-1 650(所影响的行数为 7 行)
--*/