/*Tạo 1 bảng tạm Sample1 có các field như bên dưới. */
declare @Sample1 table
(
RecordID int Primary key NOT NULL ,
ParentRecordID int,
SortOrder int,
Description nvarchar(100),
Salary money
)
/* Start loading of test data */
1. insert into @Sample1 values(1,null,null,'CEO',10)
2. insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
3. insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
4. insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
5. insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
6. insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
7. insert into @Sample1 values(7,4,1,'Human Resources Director',4)
8. insert into @Sample1 values(8,4,2,'Some other item',3)
9. insert into @Sample1 values(9,6,1,'Research Analyst',2)
/*
Hàng 1 là record gốc(cha) nên ParentRecordID = null, và SortOrder(sắp thứ tự) cũng bằng null
Hàng 2 có 1 record liên quan tức record lớn hơn(hay còn gọi là có record cha) nên ParentRecordID=1, và SortOrder khởi đầu bằng 1
Tương tự, hàng 3 cũng vậy.
Hàng 4 thì ParentRecordID=2 thì tức là record hàng 2 là cha của nó.
Cứ như vậy chèn cho đến hết các record.
Câu đệ qui trình bày giúp duyệt hết record này.
*/
set nocount off;
/*Đây là hàm đệ qui*/
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 /*Kết giữa RecordID và ParentRecordID */
)
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