-- 大概语法是这样,你自己改一下吧,这是查 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
用 CTE递归吧。去百度一下 N多的例子。
递归虽然理论上效率不算很高,不过只有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
;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 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换成你的表名
-- 大概语法是这样,你自己改一下吧,这是查 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
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
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,会查出这些的子级,不包括本身
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换成你的表名