WITH sumtb([id],[level]) AS( SELECT [pid],1 FROM [tb] A WHERE [pid]<>0 UNION ALL SELECT A.[pid],B.[level]+1 FROM [tb] A,sumtb B WHERE A.[id]=B.[id] AND A.[pid]<>0) SELECT A.[id],ChildCounts=COUNT(b.[id]) FROM [tb] A LEFT JOIN sumtb B ON A.[id]=B.[id] GROUP BY A.[id] GO
-- 6. 查询结点的所有父结点数
WITH sumtb([id],[level],[ParentCounts]) AS( SELECT [id],1,0 FROM [tb] A WHERE [pid]=0 UNION ALL SELECT A.[id],B.[level]+1,B.[ParentCounts]+1 FROM [tb] A,sumtb B WHERE A.[pid]=B.[id]) SELECT * FROM sumtb order by [ID] GO