转载 ：SQL Server 2005 Recursion and WITH Clause
Recursive queries have been added to T-SQL in SQL Server 2005 in the form of the WITH clause and CTE. The books online documentation is pretty straight forward but a few people have asked me to work up a few useful samples to help get them going. Simply copy/paste these samples into a query window and execute.
| Table of Contents Hierarchy | |
set nocount on
declare @Sample1 table
(
RecordID int Primary key NOT NULL ,
ParentRecordID int,
SortOrder int,
Description nvarchar(100),
Salary money
)
/* Start loading of test data */
insert into @Sample1 values(1,null,null,'CEO',10)
insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
insert into @Sample1 values(7,4,1,'Human Resources Director',4)
insert into @Sample1 values(8,4,2,'Some other item',3)
insert into @Sample1 values(9,6,1,'Research Analyst',2)
set nocount off;
with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary,TOC)
as
(
select RecordID,ParentRecordID,SortOrder,Salary,
convert(varchar(100),'') TOC
from @Sample1
where ParentRecordID is null
union all
select R1.RecordID,
R1.ParentRecordID,
R1.SortOrder,
R1.Salary,
case when DataLength(R2.TOC) > 0
then convert(varchar(100),R2.TOC + '.'
+ cast(R1.SortOrder as varchar(10)))
else convert(varchar(100),
cast(R1.SortOrder as varchar(10)))
end as TOC
from @Sample1 as R1
join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
)
select * from RecursionCTE order by ParentRecordID,SortOrder asc
Results
RecordID ParentRecordID SortOrder Salary TOC
----------- -------------- ----------- -----------------
1 NULL NULL 10.00
2 1 1 9.00 1
3 1 2 8.00 2
6 1 3 5.00 3
4 2 1 7.00 1.1
5 2 2 6.00 1.2
7 4 1 4.00 1.1.1
8 4 2 3.00 1.1.2
9 6 1 2.00 3.1
|
|
| Sum Up Subordinate Salaries of All Employees | |
set nocount on
declare @Sample1 table
(
RecordID int Primary key NOT NULL ,
ParentRecordID int,
SortOrder int,
Description nvarchar(100),
Salary money
)
/* Start loading of test data */
insert into @Sample1 values(1,null,null,'CEO',10)
insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
insert into @Sample1 values(7,4,1,'Human Resources Director',4)
insert into @Sample1 values(8,4,2,'Some other item',3)
insert into @Sample1 values(9,6,1,'Research Analyst',2)
set nocount off;
with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary)
as
(
select RecordID,ParentRecordID,SortOrder,Salary
from @Sample1
where ParentRecordID is null
union all
select R1.RecordID,
R1.ParentRecordID,
R1.SortOrder,
R1.Salary
from @Sample1 as R1
join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
)
select sum(R1.salary) as Salary
from @Sample1 as R1
JOIN RecursionCTE as R2
on R1.RecordID = R2.RecordID
Results
Salary
---------------------
54.00
(1 row(s) affected)
|
|
| Sum Up Subordinate Salaries of a Specific Employee | |
set nocount on
declare @Sample1 table
(
RecordID int Primary key NOT NULL ,
ParentRecordID int,
SortOrder int,
Description nvarchar(100),
Salary money
)
/* Start loading of test data */
insert into @Sample1 values(1,null,null,'CEO',10)
insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
insert into @Sample1 values(7,4,1,'Human Resources Director',4)
insert into @Sample1 values(8,4,2,'Some other item',3)
insert into @Sample1 values(9,6,1,'Research Analyst',2)
set nocount off;
with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary)
as
(
select RecordID,ParentRecordID,SortOrder,Salary
from @Sample1
where ParentRecordID =2 -- specific employee id
union all
select R1.RecordID,
R1.ParentRecordID,
R1.SortOrder,
R1.Salary
from @Sample1 as R1
join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
)
select sum(R1.salary) as Salary
from @Sample1 as R1
JOIN RecursionCTE as R2
on R1.RecordID = R2.RecordID
Results:
Salary
---------------------
20.00
|
|
| Manager to Subordinate Salary Differential | |
set nocount on
declare @Sample1 table
(
RecordID int Primary key NOT NULL ,
ParentRecordID int,
SortOrder int,
Description nvarchar(100),
Salary money
)
/* Start loading of test data */
insert into @Sample1 values(1,null,null,'CEO',10)
insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
insert into @Sample1 values(7,4,1,'Human Resources Director',4)
insert into @Sample1 values(8,4,2,'Some other item',3)
insert into @Sample1 values(9,6,1,'Research Analyst',2)
set nocount off;
with RecursionCTE (RecordID,ParentRecordID,SortOrder,ParentSalary,Salary,Differential)
as
(
select RecordID,ParentRecordID,SortOrder,
convert(money,null) as ParentSalary,
Salary,
convert(money,null) as Differential
from @Sample1
where ParentRecordID is null
union all
select R1.RecordID,
R1.ParentRecordID,
R1.SortOrder,
convert(money,R2.Salary) as ParentSalary,
R1.Salary,
convert(money,R2.Salary - R1.Salary) as Differential
from @Sample1 as R1
join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
)
select * from RecursionCTE order by ParentRecordID,SortOrder asc
RecordID ParentRecordID SortOrder ParentSalary Salary Differential
----------- -------------- ----------- --------------------- --------------------- ---------------------
1 NULL NULL NULL 10.00 NULL
2 1 1 10.00 9.00 1.00
3 1 2 10.00 8.00 2.00
6 1 3 10.00 5.00 5.00
4 2 1 9.00 7.00 2.00
5 2 2 9.00 6.00 3.00
7 4 1 7.00 4.00 3.00
8 4 2 7.00 3.00 4.00
9 6 1 5.00 2.00 3.00
|
