求sql中循环或许子级和各级总数的方法

[wenxue005]

现在有个需求 根据返回的表的ID去获取其间接的用户和各总数
表结构 tab(id int,pid int)
如图
表返回id为2和4的两条数据 根据2和4去获取间接的用户 也就是除去6,、7、8、9、10、11的所有用户 同时统计出各级的总人数
最后的结果为



用户级别为无限级
请问 这样的需求如果实现 谢谢

[还在加载中灬]

已修正

DECLARE @ID INT
SET @ID=1
;WITH CTE AS(
	SELECT id,pid
		,CAST(id AS VARCHAR(MAX))[PATH]
		,1 DEEP
	FROM tab
	WHERE pid=@ID
	UNION ALL
	SELECT T1.id,T1.pid
		,T2.[PATH]+'-'+CAST(T1.id AS VARCHAR(10))
		,T2.DEEP+1
	FROM tab T1
		JOIN CTE T2 ON T1.pid=T2.id
)
,CTE2 AS(
	SELECT T1.id,T2.id id2,T1.[PATH]
	FROM CTE T1
		JOIN CTE T2 ON T2.[PATH]+'-' LIKE T1.[PATH]+'-%'
			AND T2.DEEP>=T1.DEEP+2
	WHERE T1.DEEP%2=1
)
SELECT id
	,COUNT(1)[间接用户数量]
	,STUFF((SELECT ','+CAST(id2 AS VARCHAR(10)) FROM CTE2 T2
			WHERE T2.id=T1.id
			FOR XML PATH(''))
	,1,1,'')[包含的用户]
FROM CTE2 T1
GROUP BY id,[PATH]
ORDER BY [PATH]

[wenxue005]

引用 1 楼 ky_min 的回复:

DECLARE @ID INT
SET @ID=1
;WITH CTE AS(
	SELECT id,pid
		,id RootID
		,CAST(''AS VARCHAR(10)) id2
		,1 DEEP
	FROM tab
	WHERE pid=@ID
	UNION ALL
	SELECT T1.id,T1.pid
		,T2.RootID
		,CAST(T1.id AS VARCHAR(10))
		,T2.DEEP+1
	FROM tab T1
		JOIN CTE T2 ON T1.pid=T2.id
)
SELECT RootID ID
	,COUNT(1)[间接用户数量]
	,STUFF((SELECT ','+id2 FROM CTE T2
			WHERE T1.RootID=T2.RootID
				AND T2.DEEP>1
			FOR XML PATH(''))
	,1,1,'')[包含的用户]
FROM CTE T1
WHERE DEEP>1
GROUP BY ALL RootID

你好 多谢你的解答 你的方法只返回2和4的间接数量 但是没有返回13和14的数量 还有这个有可能是无限分级的 望不吝赐教

[wenxue005]

引用 3 楼 ky_min 的回复:

已修正

DECLARE @ID INT
SET @ID=1
;WITH CTE AS(
	SELECT id,pid
		,CAST(id AS VARCHAR(MAX))[PATH]
		,1 DEEP
	FROM tab
	WHERE pid=@ID
	UNION ALL
	SELECT T1.id,T1.pid
		,T2.[PATH]+'-'+CAST(T1.id AS VARCHAR(10))
		,T2.DEEP+1
	FROM tab T1
		JOIN CTE T2 ON T1.pid=T2.id
)
,CTE2 AS(
	SELECT T1.id,T2.id id2,T1.[PATH]
	FROM CTE T1
		JOIN CTE T2 ON T2.[PATH]+'-' LIKE T1.[PATH]+'-%'
			AND T2.DEEP>=T1.DEEP+2
	WHERE T1.DEEP%2=1
)
SELECT id
	,COUNT(1)[间接用户数量]
	,STUFF((SELECT ','+CAST(id2 AS VARCHAR(10)) FROM CTE2 T2
			WHERE T2.id=T1.id
			FOR XML PATH(''))
	,1,1,'')[包含的用户]
FROM CTE2 T1
GROUP BY id,[PATH]
ORDER BY [PATH]

多谢

[还在加载中灬]

DECLARE @ID INT
SET @ID=1
;WITH CTE AS(
	SELECT id,pid
		,id RootID
		,CAST(''AS VARCHAR(10)) id2
		,1 DEEP
	FROM tab
	WHERE pid=@ID
	UNION ALL
	SELECT T1.id,T1.pid
		,T2.RootID
		,CAST(T1.id AS VARCHAR(10))
		,T2.DEEP+1
	FROM tab T1
		JOIN CTE T2 ON T1.pid=T2.id
)
SELECT RootID ID
	,COUNT(1)[间接用户数量]
	,STUFF((SELECT ','+id2 FROM CTE T2
			WHERE T1.RootID=T2.RootID
				AND T2.DEEP>1
			FOR XML PATH(''))
	,1,1,'')[包含的用户]
FROM CTE T1
WHERE DEEP>1
GROUP BY ALL RootID

[wenxue005]

引用 1 楼 ky_min 的回复:

DECLARE @ID INT
SET @ID=1
;WITH CTE AS(
	SELECT id,pid
		,id RootID
		,CAST(''AS VARCHAR(10)) id2
		,1 DEEP
	FROM tab
	WHERE pid=@ID
	UNION ALL
	SELECT T1.id,T1.pid
		,T2.RootID
		,CAST(T1.id AS VARCHAR(10))
		,T2.DEEP+1
	FROM tab T1
		JOIN CTE T2 ON T1.pid=T2.id
)
SELECT RootID ID
	,COUNT(1)[间接用户数量]
	,STUFF((SELECT ','+id2 FROM CTE T2
			WHERE T1.RootID=T2.RootID
				AND T2.DEEP>1
			FOR XML PATH(''))
	,1,1,'')[包含的用户]
FROM CTE T1
WHERE DEEP>1
GROUP BY ALL RootID

你好 多谢你的解答 但是你的t-sql 返回的结果和问题需要的结果有些出入

[砸死牛顿的苹果]

JOIN CTE T2 ON T2.[PATH]+'-' LIKE T1.[PATH]+'-%'
==>
JOIN CTE T2 ON charindex(T1.[PATH],T2.[PATH])=1

效率是否高一些