SQL CTE 資料匯總

標籤：

if exists (select * from sysobjects where name=‘Test‘)  

　　drop table test;

go

create table Test

(

ID int, --ID

Name nvarchar(50), --名稱

Total int, --總量

FinishedTotal int, --完成量

ParentID int,--父ID

Level int--層級

)

insert into Test values(1, ‘A‘, 0,0,null,0);

insert into Test values(2, ‘AA‘, 0,0,1,1);

insert into Test values(3, ‘BB‘, 0,0,1,1);

insert into Test values(4, ‘AAA‘, 0,0,2,2);

insert into Test values(5, ‘BBB‘, 5,5,2,2);

insert into Test values(6, ‘AAAA‘, 10,10,4,3);

insert into Test values(7, ‘BBBB‘, 10,10,4,3);

insert into Test values(8, ‘CCCC‘, 10,10,4,3);

insert into Test values(9, ‘DDDD‘, 10,10,4,3);

insert into Test values(10, ‘EEEE‘, 0,0,5,3);

with Cte1 as (

select ID,Name,ParentID,Total ,FinishedTotal,Level FROM Test

union all

select T.ID,T.Name,T.[ParentID], Cte1.Total,Cte1.FinishedTotal,T.Level from Test T 

inner join

Cte1 on T.ID=Cte1.ParentID

)

select ID,Name,ParentID,T.Total ,T.FinishedTotal, A.Total as Total1,A.FinishedTotal as FinishedTotal1,Level FROM Test T

cross apply

(

select isnull(sum(Total),0) as Total,

isnull(sum(FinishedTotal),0) as FinishedTotal

from Cte1 where T.ID=Cte1.ID

) A order by ID

SQL CTE 資料匯總