表结构如下：
用户名 上级用户ID
userid rid
整个表有3-4级的上下级关系（无法确定）
现在已知某几个userid，求SQL语句扫出所有已知userid的下级（包括下级的下级 和 下级的下级的下级）
------解决思路----------------------
-- 大概语法是这样，你自己改一下吧，这是查 ID = 100 的所有下级。
with mt as
(
select * from mytable where id = 100
union all
select * from mytable x , mt where mt.id = x.rid
)
select * from mt
------解决思路----------------------
递归虽然理论上效率不算很高，不过只有3-4级还是非常快的
WITH tree AS (
SELECT userid, rid FROM userTable WHERE rid IN (1,3,5,7) --已知某几个userid
UNION ALL
SELECT u.userid, u.rid
FROM tree t
JOIN userTable u
ON u.rid = t.userid
)
SELECT * FROM tree
------解决思路----------------------
;WITH TBCTE AS(
SELECT A.userid,A.rid,B.userid RU FROM TABLENAME A LEFT JOIN TABLENAME B ON A.rid=B.userid
)
,CTE AS(
SELECT *,CAST(userid AS VARCHAR(8000))[PATH] FROM TBCTE WHERE RU IS NULL
UNION ALL
SELECT A.*,B.[PATH]+'-'+CAST(A.userid AS VARCHAR(8000)) FROM TBCTE A JOIN CTE B ON A.rid=B.userid
)
SELECT A.* FROM CTE A LEFT JOIN CTE B ON A.[PATH]LIKE B.[PATH]+'-%'WHERE B.userid IN(1,2,3)
上面小调如下
TABLENAME换成你的表名