SQL Server 2005 Recursion(递归) and WITH Clause

代码

  1  /*
  2  from: http://www.eggheadcafe.com/articles/sql_server_recursion_with_clause.asp
  3  http://msdn.microsoft.com/en-us/library/ms186243.aspx
  4  http://msdn.microsoft.com/en-us/library/aa175801%28SQL.80%29.aspx
  5  SQL Server 2005 Recursion and WITH Clause
  6  SQL Server 2005的递归和WITH子句
  7  */
  8  -- -1 Table of Contents Hierarchy
  9    set  nocount  on
 10   
 11     declare   @Sample1   table
 12    (
 13        RecordID  int    Primary   key   NOT   NULL  ,
 14        ParentRecordID  int ,
 15        SortOrder  int ,
 16        Description  nvarchar ( 100 ),
 17        Salary  money
 18     )
 19   
 20    /*  Start loading of test data  */
 21     insert   into   @Sample1   values ( 1 , null , null , ' CEO ' , 10 )
 22     insert   into   @Sample1   values ( 2 , 1 , 1 , ' Vice Pres. Marketing ' , 9 )
 23     insert   into   @Sample1   values ( 3 , 1 , 2 , ' Vice Pres. Ops- ' , 8 )
 24     insert   into   @Sample1   values ( 4 , 2 , 1 , ' Marketing Director - Direct Mail ' , 7 )
 25     insert   into   @Sample1   values ( 5 , 2 , 2 , ' Marketing Director - TV ' , 6 )
 26     insert   into   @Sample1   values ( 6 , 1 , 3 , ' Vice Pres. - Research ' , 5 )
 27     insert   into   @Sample1   values ( 7 , 4 , 1 , ' Human Resources Director ' , 4 )
 28     insert   into   @Sample1   values ( 8 , 4 , 2 , ' Some other item ' , 3 )
 29     insert   into   @Sample1   values ( 9 , 6 , 1 , ' Research Analyst ' , 2 )
 30 
 31     set  nocount  off ;
 32 
 33    with  RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary,TOC)
 34    as
 35   (
 36      select  RecordID,ParentRecordID,SortOrder,Salary,
 37             convert ( varchar ( 100 ), '' ) TOC
 38         from   @Sample1
 39         where  ParentRecordID  is   null
 40      union   all
 41      select  R1.RecordID,
 42            R1.ParentRecordID,
 43            R1.SortOrder,
 44            R1.Salary,
 45             case   when   DataLength (R2.TOC)  >   0
 46                       then   convert ( varchar ( 100 ),R2.TOC  +   ' . '
 47                                    +   cast (R1.SortOrder  as   varchar ( 10 )))
 48                       else   convert ( varchar ( 100 ),
 49                                   cast (R1.SortOrder  as   varchar ( 10 )))
 50                       end   as  TOC
 51         from   @Sample1   as  R1
 52        join  RecursionCTE  as  R2  on  R1.ParentRecordID  =  R2.RecordID
 53    )
 54 
 55  select   *   from  RecursionCTE  order   by  ParentRecordID,SortOrder  asc
 56 
 57  -- 2 Sum Up Subordinate Salaries of All Employees
 58 
 59  set  nocount  on
 60   
 61     declare   @Sample1   table
 62    (
 63        RecordID  int    Primary   key   NOT   NULL  ,
 64        ParentRecordID  int ,
 65        SortOrder  int ,
 66        Description  nvarchar ( 100 ),
 67        Salary  money
 68     )
 69   
 70    /*  Start loading of test data  */
 71     insert   into   @Sample1   values ( 1 , null , null , ' CEO ' , 10 )
 72     insert   into   @Sample1   values ( 2 , 1 , 1 , ' Vice Pres. Marketing ' , 9 )
 73     insert   into   @Sample1   values ( 3 , 1 , 2 , ' Vice Pres. Ops- ' , 8 )
 74     insert   into   @Sample1   values ( 4 , 2 , 1 , ' Marketing Director - Direct Mail ' , 7 )
 75     insert   into   @Sample1   values ( 5 , 2 , 2 , ' Marketing Director - TV ' , 6 )
 76     insert   into   @Sample1   values ( 6 , 1 , 3 , ' Vice Pres. - Research ' , 5 )
 77     insert   into   @Sample1   values ( 7 , 4 , 1 , ' Human Resources Director ' , 4 )
 78     insert   into   @Sample1   values ( 8 , 4 , 2 , ' Some other item ' , 3 )
 79     insert   into   @Sample1   values ( 9 , 6 , 1 , ' Research Analyst ' , 2 )
 80 
 81  set  nocount  off ;
 82 
 83    with  RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary)
 84    as
 85   (
 86      select  RecordID,ParentRecordID,SortOrder,Salary
 87         from   @Sample1
 88         where  ParentRecordID  is   null
 89      union   all
 90      select  R1.RecordID,
 91            R1.ParentRecordID,
 92            R1.SortOrder,
 93            R1.Salary
 94         from   @Sample1   as  R1
 95        join  RecursionCTE  as  R2  on  R1.ParentRecordID  =  R2.RecordID
 96    )
 97    select   sum (R1.salary)  as  Salary
 98      from   @Sample1   as  R1
 99      JOIN  RecursionCTE  as  R2
100      on  R1.RecordID  =  R2.RecordID
101  -- 3  Sum Up Subordinate Salaries of a Specific Employee
102  set  nocount  on
103   
104     declare   @Sample1   table
105    (
106        RecordID  int    Primary   key   NOT   NULL  ,
107        ParentRecordID  int ,
108        SortOrder  int ,
109        Description  nvarchar ( 100 ),
110        Salary  money
111     )
112   
113    /*  Start loading of test data  */
114     insert   into   @Sample1   values ( 1 , null , null , ' CEO ' , 10 )
115     insert   into   @Sample1   values ( 2 , 1 , 1 , ' Vice Pres. Marketing ' , 9 )
116     insert   into   @Sample1   values ( 3 , 1 , 2 , ' Vice Pres. Ops- ' , 8 )
117     insert   into   @Sample1   values ( 4 , 2 , 1 , ' Marketing Director - Direct Mail ' , 7 )
118     insert   into   @Sample1   values ( 5 , 2 , 2 , ' Marketing Director - TV ' , 6 )
119     insert   into   @Sample1   values ( 6 , 1 , 3 , ' Vice Pres. - Research ' , 5 )
120     insert   into   @Sample1   values ( 7 , 4 , 1 , ' Human Resources Director ' , 4 )
121     insert   into   @Sample1   values ( 8 , 4 , 2 , ' Some other item ' , 3 )
122     insert   into   @Sample1   values ( 9 , 6 , 1 , ' Research Analyst ' , 2 )
123 
124  set  nocount  off ;
125 
126    with  RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary)
127    as
128   (
129      select  RecordID,ParentRecordID,SortOrder,Salary
130         from   @Sample1
131         where  ParentRecordID  = 2   --  specific employee id
132      union   all
133      select  R1.RecordID,
134            R1.ParentRecordID,
135            R1.SortOrder,
136            R1.Salary
137         from   @Sample1   as  R1
138        join  RecursionCTE  as  R2  on  R1.ParentRecordID  =  R2.RecordID
139    )
140    select   sum (R1.salary)  as  Salary
141      from   @Sample1   as  R1
142      JOIN  RecursionCTE  as  R2
143      on  R1.RecordID  =  R2.RecordID
144 
145  -- 4  Manager to Subordinate Salary Differential
146 
147  set  nocount  on
148   
149     declare   @Sample1   table
150    (
151        RecordID  int    Primary   key   NOT   NULL  ,
152        ParentRecordID  int ,
153        SortOrder  int ,
154        Description  nvarchar ( 100 ),
155        Salary  money
156     )
157   
158    /*  Start loading of test data  */
159     insert   into   @Sample1   values ( 1 , null , null , ' CEO ' , 10 )
160     insert   into   @Sample1   values ( 2 , 1 , 1 , ' Vice Pres. Marketing ' , 9 )
161     insert   into   @Sample1   values ( 3 , 1 , 2 , ' Vice Pres. Ops- ' , 8 )
162     insert   into   @Sample1   values ( 4 , 2 , 1 , ' Marketing Director - Direct Mail ' , 7 )
163     insert   into   @Sample1   values ( 5 , 2 , 2 , ' Marketing Director - TV ' , 6 )
164     insert   into   @Sample1   values ( 6 , 1 , 3 , ' Vice Pres. - Research ' , 5 )
165     insert   into   @Sample1   values ( 7 , 4 , 1 , ' Human Resources Director ' , 4 )
166     insert   into   @Sample1   values ( 8 , 4 , 2 , ' Some other item ' , 3 )
167     insert   into   @Sample1   values ( 9 , 6 , 1 , ' Research Analyst ' , 2 )
168 
169  set  nocount  off ;
170 
171    with  RecursionCTE (RecordID,ParentRecordID,SortOrder,ParentSalary,Salary,Differential)
172    as
173   (
174      select  RecordID,ParentRecordID,SortOrder,
175               convert ( money , null )  as  ParentSalary,
176              Salary,
177               convert ( money , null )  as  Differential
178         from   @Sample1
179         where  ParentRecordID  is   null
180      union   all
181      select  R1.RecordID,
182              R1.ParentRecordID,
183              R1.SortOrder,
184               convert ( money ,R2.Salary)  as  ParentSalary,
185              R1.Salary,
186               convert ( money ,R2.Salary  -  R1.Salary)  as  Differential
187         from   @Sample1   as  R1
188        join  RecursionCTE  as  R2  on  R1.ParentRecordID  =  R2.RecordID
189     
190       
191    )
192 
193  select   *   from  RecursionCTE  order   by  ParentRecordID,SortOrder  asc