--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 @t
select 1, 1,'user1',1,1 union all
select 2, 2,'user2',1,1 union all
select 3, 3,'user3',2,0 union all
select 4, 3,'user3',2,0 union all
select 5, 4,'user4',3,0 union all
select 6, 5,'user5',1,1 union all
select 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' ) f
1, user1, 1, 0
2, user2, 1, 3
5, user5, 1, 1
*/
--不包含应该是这样，
/*
uId, userName, count(case status ='true' ) t, count(case status ='false' ) f
2, user2, 1, 3
5, 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 @t
select 1, 1,'user1',0,1 union all
select 2, 2,'user2',1,1 union all
select 3, 3,'user3',2,0 union all
select 4, 3,'user3',2,0 union all
select 5, 4,'user4',3,0 union all
select 6, 5,'user5',1,1 union all
select 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 userName=@p
union all
select 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 t
join @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) f
from tb2 t1
where status=1
group by [uId],userName

--输出结果如果包含user1的情况
/*
uId  userName t           f
---- -------- ----------- -----------
1    user1    1           0
2    user2    1           3
5    user5    1           1
*/

--不包含user1的情况时,最后where条件中加上uid<>1即可
/*
uId  userName t           f
---- -------- ----------- -----------
2    user2    1           3
5    user5    1           1
*/