具有三个表的递归CTE

Question

我正在使用SQL Server 2008 R2 SP1。我想通过“走树”来递归地为某个组织单位找到第一个非空经理。

我有一个包含组织单位“ ORG”的表，一个包含每个组织的父母的表。“ ORG”中的部门，将其称为表“ORG_PARENTS”，其中一个表包含每个组织部门的经理，将其称为“ ORG_MANAGERS”。

ORG具有一列ORG_ID：

ORG_ID

1

2

3

ORG_PARENTS有两列。

ORG_ID, ORG_PARENT

1, NULL

2, 1

3, 2

管理器有两列。

ORG_ID, MANAGER

1, John Doe

2, Jane Doe

3, NULL

我正在尝试创建一个递归查询，该查询将为某个组织单位找到第一个非空管理器。

基本上，如果我今天查询经理ORG_ID = 3，我将得到NULL。

SELECT MANAGER FROM ORG_MANAGERS WHERE ORG_ID = '3'

我希望查询使用ORG_PARENTS表获取ORG_ID = 3的父级，在这种情况下，获取“ 2”并针对ORG_ID = 2的ORG_MANAGERS表重复查询，并在此示例中返回“ Jane Doe”。

如果查询还返回NULL，我想使用ORG_ID = 2的父级（即ORG_ID = 1，以此类推）重复该过程。

到目前为止，我的CTE尝试失败了，一个例子是：

WITH BOSS (MANAGER, ORG_ID, ORG_PARENT)
AS
( SELECT m.MANAGER, m.ORG_ID, p.ORG_PARENT
FROM dbo.MANAGERS m INNER JOIN
dbo.ORG_PARENTS p ON p.ORG_ID = m.ORG_ID
UNION ALL
SELECT m1.MANAGER, m1.ORG_ID, b.ORG_PARENT
FROM BOSS b
INNER JOIN dbo.MANAGERS m1 ON m1.ORG_ID = b.ORG_PARENT
)

SELECT * FROM BOSS WHERE ORG_ID = 3

它返回：

消息530，级别16，状态1，第4行语句终止。在语句完成之前，最大递归100已用尽。

MANAGER ORG_ID  ORG_PARENT
NULL      3        2

Answer 1

您需要跟踪开始时使用的原始ID。试试这个：

DECLARE @ORG_PARENTS TABLE (ORG_ID INT, ORG_PARENT INT ) 
DECLARE @MANAGERS TABLE (ORG_ID INT, MANAGER VARCHAR(100))

INSERT @ORG_PARENTS (ORG_ID, ORG_PARENT)
VALUES  (1, NULL)
,       (2, 1)
,       (3, 2)

INSERT @MANAGERS (ORG_ID, MANAGER)
VALUES (1, 'John Doe')
,       (2, 'Jane Doe')
,       (3, NULL)
;
WITH BOSS
AS
( 
    SELECT      m.MANAGER, m.ORG_ID AS ORI, m.ORG_ID, p.ORG_PARENT, 1 cnt
    FROM        @MANAGERS m 
    INNER JOIN  @ORG_PARENTS p 
                ON p.ORG_ID = m.ORG_ID
    UNION ALL

    SELECT      m1.MANAGER, b.ORI, m1.ORG_ID, OP.ORG_PARENT, cnt +1
    FROM        BOSS b
    INNER JOIN  @ORG_PARENTS AS OP
            ON  OP.ORG_ID = b.ORG_PARENT
    INNER JOIN  @MANAGERS m1 
            ON  m1.ORG_ID = OP.ORG_ID 
)

SELECT  * 
FROM    BOSS 
WHERE   ORI = 3

结果是：

+----------+-----+--------+------------+-----+
| MANAGER  | ORI | ORG_ID | ORG_PARENT | cnt |
+----------+-----+--------+------------+-----+
| NULL     |   3 |      3 | 2          |   1 |
| Jane Doe |   3 |      2 | 1          |   2 |
| John Doe |   3 |      1 | NULL       |   3 |
+----------+-----+--------+------------+-----+

一般提示：

不要预定义CTE的列；这是不必要的，并且使维护变得烦人。

使用递归CTE时，始终要保留一个计数器，这样您就可以限制递归性，并且可以跟踪自己的深度。

编辑：

顺便说一句，如果您想要第一个不是null的管理器，则可以这样做（例如，有很多方法）：

SELECT  BOSS.* 
FROM    BOSS 
INNER JOIN (
                SELECT  BOSS.ORI
                ,       MIN(BOSS.cnt) cnt
                FROM    BOSS
                WHERE   BOSS.MANAGER IS NOT NULL
                GROUP BY BOSS.ORI 
            ) X
        ON  X.ORI = BOSS.ORI
        AND X.cnt = BOSS.cnt
WHERE   BOSS.ORI IN (3)