SQL recursive query for all subordinates

--> 生成测试数据表: [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 recursive query for all subordinates