--查询Id为1的所有子节点层数的数量
;with cte as
(
select id,parentId,0 as Leave from _TestLeave where id = 1 --要查询的节点
union all
select a.id,a.parentId,b.Leave + 1 as Leave from _TestLeave a join cte b on a.parentId = b.id
and b.Leave < 6 --层数
)
select leave,count(*) from cte where leave > 0 group by Leave