具有三个表的递归CTE

尼姑拉丝

我正在使用SQL Server 2008 R2 SP1.我想通过走树为组织单位找到第一个非空经理。
我有一个组织单位 ORG表，一个包含每个组织的父母的表。ORG部门称之为表
7 ^3 \0 ~" l! x5 p6 r4 p& n. ]ORG_PARENTS”，其中一个表包含每个组织部门的经理，将其称为“ ORG_MANAGERS”。
9 s# u  u  o( U# B$ Z4 w7 lORG具有一列ORG_ID：
5 a- x# o+ s1 H3 B( F8 u1 gORG_ID123ORG_PARENTS有两列。
& E! P9 S4 [" n) C' IORG_ID,ORG_PARENT1,NULL2,13,2管理器有两列。
ORG_ID,MANAGER1,John Doe2,Jane Doe3,NULL我正图创建一个递归查询，它将为组织找到第一个非空管理器。
基本上，如果我今天查询经理ORG_ID = 3，我会得到的NULL。
SELECT MANAGER FROM ORG_MANAGERS WHERE ORG_ID = '3'希望查询使用情况ORG_PARENTS表获取ORG_ID = 3的父级，在这种情况下，获得 2并针对ORG_ID =
# V. H2 }/ i! @2的ORG_MANAGERS在这个例子中，表重复查询并返回 Jane Doe”。
如查询返回NULL，我想使用ORG_ID = 2父级(即ORG_ID = 1，以此类推)重复此过程。
. m, e( V4 X) F; ^0 p  s6 k2 U6 U, Y到目前为止，我的CTE一个尝试失败的例子是:
$ I5 z/ Q* r. U8 GWITH BOSS (MANAGER,ORG_ID,ORG_PARENT)AS( SELECT m.MANAGER,m.ORG_ID,p.ORG_PARENTFROM dbo.MANAGERS m INNER JOINdbo.ORG_PARENTS p ON p.ORG_ID = m.ORG_IDUNION ALLSELECT m1.MANAGER,m1.ORG_ID,b.ORG_PARENTFROM BOSS bINNER JOIN dbo.MANAGERS m1 ON m1.ORG_ID = b.ORG_PARENT)SELECT * FROM BOSS WHERE ORG_ID = 3它返回：
新闻530，级别16，状态1，第4行语句终止。最大递归100在语句完成前已耗尽。
  [. S7 Z- V& ]2 h9 dMANAGER ORG_ID  ORG_PARENTNULL                                                         
    解决方案:                                                               
+ x0 i0 r7 @# v3 b) n                                                                您需要跟踪开始时使用的原始用途ID。试试这个：
; m4 G: ~( a8 c( F  {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),        INSERT @MANAGERS (ORG_ID,MANAGER)VALUES (1,'John Doe'),      (2,'Jane Doe NULL);WITH BOSSAS(     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 m                                                                                                                                                                                                                                          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 |             |   2 || John Doe |   3 |      1 | NULL       |   3 | ---------- ----- -------- ------------ ----- 一般提示：
- [0 S; u& D' y/ ]( ^: M, e不要预定义CTE列；这是不必要的，使维护烦人。
使用递归CTE始终保留一个计数器，这样你就可以限制递归性，并跟踪你的深度。
: b" u1 O4 P$ ~7 u2 `* x编辑：
/ |# r" o9 ^) Z% i+ y顺便说一句，如果你想要第一个，那就不是了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.cntWHERE   BOSS.ORI IN (3)