Đệ qui trong MS SQL 2005

 

/*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