SQL遞歸應用
Code-- =============================================================================
-- 標題: SQL遞歸
-- 原創: takako_mu
-- 时间: 2009-10-19
-- 地点: 昆山
-- =============================================================================
create table #tb(DeptCode nvarchar(10),ParentCode nvarchar(10),Percentage float)
insert into #tb
select '0__Lev1','0000000',1 union all
select '1__Lev1','0__Lev1',0.8 union all
select '1__Lev2','0__Lev1',0.9 union all
select '2__Lev1','1__Lev1',0.7 union all
select '2__Lev2','1__Lev2',0.6 union all
select '3__Lev1','2__Lev1',0.5
/*#tb內容如下
DeptCode ParentCode Percentage
---------- ---------- ----------------------
0__Lev1 0000000 1
1__Lev1 0__Lev1 0.8
1__Lev2 0__Lev1 0.9
2__Lev1 1__Lev1 0.7
2__Lev2 1__Lev2 0.6
3__Lev1 2__Lev1 0.5
*/
----------------------------------------------------------------
--測試
with B as
(
select DeptCode,ParentCode,0 as [Level],Percentage as Percentage,Percentage as Sharing from #tb where ParentCode='0000000'
union all
select A.DeptCode,A.ParentCode,[Level]+1 as [Level],A.Percentage,A.Percentage*B.Sharing as Sharing from #tb A inner join B on A.ParentCode=B.DeptCode
)
select * from B
/*#tb
DeptCode ParentCode Level Percentage Sharing
---------- ---------- ----------- ---------------------- ----------------------
0__Lev1 0000000 0 1 1
1__Lev1 0__Lev1 1 0.8 0.8
1__Lev2 0__Lev1 1 0.9 0.9
2__Lev2 1__Lev2 2 0.6 0.54
2__Lev1 1__Lev1 2 0.7 0.56
3__Lev1 2__Lev1 3 0.5 0.28
*/
