递归查询用户所在层级

1、用户表

2、查询用户所在层级语句

![在这里插入代码片](https://img-blog.csdnimg.cn/direct/651d06f0a8a04f93b72f92972d79702c.png)
WITH RECURSIVE UserHierarchy AS (
    SELECT user_uid, xm, invite_user_id, 1 AS level
    FROM dzhy
    WHERE invite_user_id = 0
    UNION ALL
    SELECT d.user_uid, d.xm, d.invite_user_id, uh.level + 1
    FROM dzhy d
    JOIN UserHierarchy uh ON d.invite_user_id::integer = uh.user_uid::integer
)
SELECT user_uid, xm, level
FROM UserHierarchy
ORDER BY level, user_uid;

3、运行结果