在SQL中执行递归查询以级联方式获取数据

Question

假设您有一个用户表。用户分配了一个角色和一个referer字段，该字段是具有更高角色的其他用户的ID。让我们假设我们有5个角色，其中第五个角色较高。referer id始终是添加该用户的用户的id，并且它只能在更高的一个角色中

我想获得角色5以下的所有用户(从角色4开始(，但他们必须由用户或我的用户引用的用户引用，以此类推。示例：

User1: [ role: 5, referer: 0 (because is the first user), id: 1]
User2: [ role: 4, referer: 1, id:2]
User3: [ role: 4, referer: 44, id: 3]
User4: [ role: 3, referer: 2, id: 4]
User6: [ role: 2, referer: 4, id: 5]
User7: [ role: 1, referer: 5, id: 6]
User8: [ role: 1, referer: 44, id: 7]

因此，我想使用User1数据进行查询，并且响应应该具有User2、User4、User6和User7。

如有任何帮助，我们将不胜感激。

Answer 1

使用recursive CTE

with cte as
(Select * from tbl where id=1
union all 
Select u.id, u.role,u.referer from tbl u
join cte c on c.id = u.referer
)
Select * from cte where role <(Select role from tbl where id=1)