父子节点数据统计

--测试环境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