递归之后按照等级显示的SQL

[浊酒伴清风]

CREATE TABLE [Users](
[UserID] [bigint],
[LoginName] [nvarchar](50),
[FatherUserID] [bigint]
)

userid name FatherUserID
01 a1 0
0102 b2 01
010201 c3 0102
最终显示
userid level1 level2 level3
010201 c3 b2 a1

[中国风]

固定三级时 e.g.

SELECT a.[LoginName]
     , b.[LoginName]
     , c.[LoginName]
FROM [Users] AS a
     INNER JOIN [Users] AS b ON b.[UserID]=a.[FatherUserID]
     INNER JOIN [Users] AS c ON c.[UserID]=b.[FatherUserID]

[中国风]

如果最多三级时这样用,以你的例子三条记录都显示 e.g.

SELECT a.[LoginName]
     , b.[LoginName]
     , c.[LoginName]
FROM [Users] AS a
     LEFT JOIN [Users] AS b ON b.[UserID]=a.[FatherUserID]
     LEFT JOIN [Users] AS c ON c.[UserID]=b.[FatherUserID]