如何用递归CTE查询替换COALESCE？ [英] How to replace COALESCE with recursive CTE query?

问题描述

具有三个表的递归CTE的后续问题对我有帮助SQL Server中的CTE。

A follow-up question to Recursive CTE with three tables which helped me with CTE's in SQL Server.

最初的问题有什么变化？表 MANAGERS 不再包含没有管理者的org.units的行。

What has changed from the initial question? The table MANAGERSno longer includes rows for org.units that have no manager.

目标是获取组织单位的第一个非空经理。我已经使用COALESCE和OUTER JOIN使其工作了，我的问题是是否可以使用递归查询？

The goal is to get the first non-null manager for a organizational unit. I've got it working using COALESCE and OUTER JOIN's, my question is if it is possible to use a recursive query instead?

下面的示例代码。

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

INSERT @ORG (ORG_ID, ORG_NAME)
VALUES  (1, 'One')
,       (2, 'Two')
,       (3, 'Three')

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')

;
-- The original answer
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

-- The following query only returns ORG "One" and "Two"
SELECT ORG.ORG_ID as "ORGID", ORG.ORG_NAME AS "NAME", MGR.MANAGER AS "MANAGER"
FROM @ORG ORG INNER JOIN @MANAGERS MGR ON MGR.ORG_ID = ORG.ORG_ID

-- The following query returns three rows, with NULL as the MANAGER for "Three"
-- My goal is to get the manager from the first parent that is not null
SELECT ORG.ORG_ID as "ORGID", ORG.ORG_NAME AS "NAME", (SELECT MANAGER FROM     @MANAGERS M WHERE M.ORG_ID = ORG.ORG_ID) AS "MANAGER"
FROM @ORG ORG 


-- The following workaround works, I can use COALESCE and a large number of OUTER JOINs to get the desired effect.
-- Is it possible to replace the code below with a recursive query, optimally where the number of levels is not hardcoded?
SELECT ORG.ORG_ID as "ORGID", ORG.ORG_NAME AS "NAME",
COALESCE(
      (SELECT MANAGER FROM @MANAGERS M WHERE M.ORG_ID = ORG.ORG_ID),
      (SELECT MANAGER FROM @MANAGERS M WHERE M.ORG_ID = p1.ORG_PARENT),
      (SELECT MANAGER FROM @MANAGERS M WHERE M.ORG_ID = p2.ORG_PARENT)
    ) AS "MANAGER"
FROM @ORG ORG 
LEFT OUTER JOIN @ORG_PARENTS p1 ON ORG.ORG_ID = p1.ORG_ID
LEFT OUTER JOIN @ORG o1 ON p1.ORG_PARENT = o1.ORG_ID
LEFT OUTER JOIN @ORG_PARENTS p2 ON o1.ORG_ID = p2.ORG_ID
LEFT OUTER JOIN @ORG o2 ON p2.ORG_PARENT = o2.ORG_ID

预期结果

我希望3的MANAGER列显示 Jane Doe。例如，COALSECE（上面的最后一个选择）就是这样的：

I want the MANAGER column for 3 to show "Jane Doe". For example the COALSECE (last SELECT above) does that:

ORGID   NAME    MANAGER
1       One     John Doe
2       Two     Jane Doe
3       Three   Jane Doe

推荐答案

@ZLK在他/她的评论中是正确的，您不需要MANAGERS表来执行递归：

@ZLK is right in his/her comment, you don't need the MANAGERS table to execute the recursion:

WITH BOSS
AS
( 
SELECT      P.ORG_ID AS ORI, P.ORG_ID, p.ORG_PARENT, 1 cnt
FROM        @ORG_PARENTS p 
UNION ALL

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

SELECT      * 
FROM        BOSS 
LEFT JOIN   @MANAGERS m1 
        ON  m1.ORG_ID = BOSS.ORG_ID 
WHERE       ORI = 3

结果：

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

这篇关于如何用递归CTE查询替换COALESCE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！