父子節點資料統計

huyg發表於2017-10-26
--測試環境MSSQL2008
--建表
Create Table M
(
    Id int,
    Name Varchar(20),
    PId int
)
 
Create Table N
(
    Id int,
    DesContent Varchar(20),
    M_Id int,
    TotalMoney int
)
 
--插入資料
Insert into M values(1,'集團',0)
Insert into M values(2,'第一公司',1)
Insert into M values(3,'第二公司',1)
Insert into M values(4,'第三公司',1)
Insert into M values(5,'人力資源',2)
Insert into M values(6,'行政部',2)
Insert into M values(7,'政治部',2)
Insert into M values(8,'人力資源',3)
Insert into M values(9,'行政部',3)
Insert into M values(10,'政治部',3)
Insert into M values(11,'人力資源',4)
Insert into M values(12,'行政部',4)
Insert into M values(13,'政治部',4)
insert into N Values(1,'內容1',5,10)
insert into N Values(2,'內容2',5,10)
insert into N Values(3,'內容3',5,10)
insert into N Values(4,'內容4',6,10)
insert into N Values(5,'內容5',6,10)
insert into N Values(6,'內容6',6,10)
insert into N Values(7,'內容7',6,10)
insert into N Values(8,'內容8',7,10)
insert into N Values(9,'內容9',7,10)
insert into N Values(10,'內容10',8,10)
insert into N Values(11,'內容11',9,10)
insert into N Values(12,'內容12',11,10)
 
--查詢 
With CT
AS
(
    select M.*,N.TotalMoney,N.Id As N_Id  from M  left join N on M.Id=N.M_Id 
    union all
    select M.*,CT.TotalMoney,CT.N_Id from CT inner join M 
    on CT.PId=M.Id
)
Select Id,Name,COUNT(N_Id) As iCount,sum(TotalMoney) As TotalMoney From CT
Group by  
Id,Name
order by Id

 



--只顯示IdPId的合計
With CT
AS
(
    select M.*,N.TotalMoney,N.Id As N_Id  from M  left join N on M.Id=N.M_Id 
    union all
    select M.*,CT.TotalMoney,CT.N_Id from CT inner join M 
    on CT.PId=M.Id
)
Select Id,Name,COUNT(N_Id) As iCount,sum(TotalMoney) As TotalMoney From CT
where Id   in (select PId from  M )  
Group by  
Id,Name
order by Id

 





 




相關文章