首页 > 代码库 > sql 递归查询所有的下级
sql 递归查询所有的下级
--> 生成测试数据表: [tb]
IF OBJECT_ID(
‘[Users]‘
)
IS
NOT
NULL
DROP
TABLE
[Users]
GO
CREATE
TABLE
[Users] ([userid] [
int
],[username] [nvarchar](10),[parentUserId] [
int
],[parentUserName] [nvarchar](10))
INSERT
INTO
[Users]
SELECT
‘1‘
,
‘admin‘
,
‘0‘
,
NULL
UNION
ALL
SELECT
‘2‘
,
‘aaaaa‘
,
‘1‘
,
‘admin‘
UNION
ALL
SELECT
‘3‘
,
‘bbbbb‘
,
‘2‘
,
‘aaaaa‘
UNION
ALL
SELECT
‘4‘
,
‘ddddd‘
,
‘3‘
,
‘bbbbb‘
-->SQL查询如下:
;
with
t
as
(
select
*,
level
=1
from
Users
where
[parentUserId]=0
union
all
select
a.*,
level
+1
from
Users a
join
t b
on
a.parentUserId=b.userid
)
select
*
from
t
where
[parentUserId]<>0
/*
userid username parentUserId parentUserName level
----------- ---------- ------------ -------------- -----------
2 aaaaa 1 admin 2
3 bbbbb 2 aaaaa 3
4 ddddd 3 bbbbb 4
(3 行受影响)
*/
sql 递归查询所有的下级
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。