sql-server - SQL 递归 CTE - 保留对父级的引用

Question

我有一个具有以下结构的表

userId      userName         managerId
----------- ---------------- -----------
1           John             NULL
2           Charles          1
3           Nicolas          NULL
4           Neil             3

我还有另一个表，其中包含以下内容

userId      shareId         
----------- -----------
1           1001             
3           1002               

因此，我执行以下查询来获取递归 CTE:

WITH UserCTE AS (
  SELECT userId, userName, managerId,0 AS steps
  FROM dbo.Users
  WHERE userId = 7

  UNION ALL

  SELECT mgr.userId, mgr.userName, mgr.managerId, usr.steps +1 AS steps
  FROM UserCTE AS usr
    INNER JOIN dbo.Users AS mgr
      ON usr.managerId = mgr.userId
)
SELECT * FROM UserCTE AS u;         

产生以下结果

userId      userName         managerId   steps       
----------- ---------------- ----------- ----------- 
1           John             NULL        0           
2           Charles          1           1           
3           Nicolas          NULL        0           
4           Neil             3           1      

好的，所以我想知道的是拥有它的用户以及属于他们的用户的 shareId。

预期结果:

userId      userName         managerId   steps       shareId
----------- ---------------- ----------- ----------- ----------
1           John             NULL        0           1001
2           Charles          1           1           1001
3           Nicolas          NULL        0           1002
4           Neil             3           1           1002

有什么办法可以实现吗？

谢谢

Answer 1

将共享表与递归CTE的 anchor 查询中的Users表连接起来。试试这个。

;WITH UserCTE
     AS (SELECT c.userId,
                userName,
                managerId,
                0 AS steps,
                shareId
         FROM   dbo.Users c
                LEFT JOIN share_table s
                  ON c.userId = s.userId
         WHERE  managerId IS NULL
         UNION ALL
         SELECT mgr.userId,
                mgr.userName,
                mgr.managerId,
                usr.steps + 1 AS steps,
                usr.shareId
         FROM   UserCTE AS usr
                INNER JOIN dbo.Users AS mgr
                        ON usr.userId = mgr.managerId)
SELECT *
FROM   UserCTE AS u
ORDER  BY userId;