树形汇总的SQl语句

--ID 为主键，自增长的，没什么意义； uId为用户标识；userName 为用户名；parentUId 为父级 uId； status 为统计状态； 一个用户可以重复多条数据；顶级用户的父级为自己declare @t table (ID varchar(1),uId varchar(1),userName varchar(5),parentUId varchar(1),status bit)insert into @tselect 1, 1,'user1',1,1 union allselect 2, 2,'user2',1,1 union allselect 3, 3,'user3',2,0 union allselect 4, 3,'user3',2,0 union allselect 5, 4,'user4',3,0 union allselect 6, 5,'user5',1,1 union allselect 7, 6,'user6',5,0--假定当前用户为user1,准备统计user1下的所有数据：按照status分类并汇总到user1的儿子上Declare @p varchar(6) set @p='user1'--输出结果如果包含user1应该是/*uId, userName, count(case status ='true' ) t, count(case status ='false' ) f1, user1, 1, 02, user2, 1, 35, user5, 1, 1*/--不包含应该是这样，/*uId, userName, count(case status ='true' ) t, count(case status ='false' ) f2, user2, 1, 35, user5, 1, 1*/

--ID 为主键，自增长的，没什么意义； uId为用户标识；userName 为用户名；parentUId 为父级 uId； status 为统计状态； 一个用户可以重复多条数据；顶级用户的父级为自己declare @t table (ID varchar(1),uId varchar(1),userName varchar(5),parentUId varchar(1),status bit)insert into @tselect 1, 1,'user1',0,1 union allselect 2, 2,'user2',1,1 union allselect 3, 3,'user3',2,0 union allselect 4, 3,'user3',2,0 union allselect 5, 4,'user4',3,0 union allselect 6, 5,'user5',1,1 union allselect 7, 6,'user6',5,0--假定当前用户为user1,准备统计user1下的所有数据：按照status分类并汇总到user1的儿子上Declare @p varchar(6) set @p='user1';--CTE递归查询展示层次结构with tb1 as(select ID,[uId],userName,parentUId,[status],[level]=0,tru=(case when [status]=1 then uId end),fal=(case when [status]=0 then uid end)from @t where [email protected]union allselect t1.ID,t1.uId,t1.userName,t1.parentUId,t1.[status],[level]+1,(case when t1.[status]=1 and [level]=0 then t.[uId] when t1.[status]=1 and [level]=1 then t.[uId] when t1.[status]=1 and level<>1 then t.parentUId end),(case when t1.[status]=0 and [level]=0 then t.[uId] when t1.[status]=0 and [level]=1 then t.[uId] when t1.[status]=0 and level<>1 then t.parentUId end)from tb1 tjoin @t t1 on t1.parentUId=t.[uId]),tb2 as(select distinct * from tb1)select [uId],userName,COUNT(tru) t,(select COUNT(fal) from tb2 where fal=t1.uId) ffrom tb2 t1where status=1group by [uId],userName--输出结果如果包含user1的情况/*uId  userName t           f---- -------- ----------- -----------1    user1    1           02    user2    1           35    user5    1           1*/--不包含user1的情况时,最后where条件中加上uid<>1即可/*uId  userName t           f---- -------- ----------- -----------2    user2    1           35    user5    1           1*/