--> 生成测试数据表: [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 行受影响)
*/