如何连接同一表中的两列分层数据

Question

我有一个表UserType

tbody> <<tr>24几个5

ID	UserType	ChildUserType
1	客户	空
	销售	客户
3	经理	销售
	超级管理员	管理
	管理	经理

我正在检查您的请求，我认为以下查询的结果如您所期望的，查询被更新为遵循您的规则链父级和子级注册

WITH table1  AS (
SELECT * FROM (
VALUES
(1,'Customer', ''), 
(2,'Sale', 'Customer'), 
(3,'Manager', 'Sale'), 
(4,'SuperAdmin', 'Admin'),
(5,'Admin', 'Manager')
) AS _ (ID,UserType,ChildUserType)
),
Table2 AS (
SELECT ID
,UserType
,ChildUserType
FROM table1
WHERE UserType = 'SuperAdmin' --Update this variable to get the result
UNION ALL
SELECT
t1.ID
,t1.UserType
,t1.ChildUserType
FROM
table1 t1
INNER JOIN Table2 T2
ON t1.UserType = T2.ChildUserType
)
SELECT ChildUserType as UserType
FROM Table2  where Len(ChildUserType) > 0

Usertype = SuperAdmin的查询结果

UserType
Admin
Manager
Sale
Customer

Usertype = Manager的查询结果

UserType
Sale
Customer

问好