父子节点数据统计-白红宇

父子节点数据统计

阅读量：6811 次

发布时间：2019-06-26

本文共 1661 字，大约阅读时间需要 5 分钟。

--测试环境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 CTAS(    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 CTGroup by  Id,Nameorder by Id

--只显示IdPId的合计With CTAS(    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 CTwhere Id   in (select PId from  M )  Group by  Id,Nameorder by Id