22,209
社区成员
发帖
与我相关
我的任务
分享
;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换成你的表名;WITHTBCTE 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)
你可以参考一下,把最后面的 1 2 3换成你的userid,会查出这些的子级,不包括本身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
-- 大概语法是这样,你自己改一下吧,这是查 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