具有三个表的递归CTE [英] Recursive CTE with three tables
问题描述
我正在使用SQL Server 2008 R2 SP1。
我想通过走树来递归地为某个组织单位找到第一个非空经理。
I'm using SQL Server 2008 R2 SP1. I would like to recursively find the first non-null manager for a certain organizational unit by "walking up the tree".
我有一个包含组织的表单位 ORG，其中包含每个组织的上级表格。
I have one table containing organizational units "ORG", one table containing parents for each org. unit in "ORG", lets call that table "ORG_PARENTS" and one table containing managers for each organizational unit, lets call that table "ORG_MANAGERS".
ORG有一个列，其中 ORG中的单位称为 ORG_PARENTS，一个表包含每个组织单位的经理。该表称为 ORG_MANAGERS。 ORG_ID：
ORG has a column ORG_ID:
ORG_ID
1
2
3
ORG_PARENTS有两列。
ORG_PARENTS has two columns.
ORG_ID, ORG_PARENT
1, NULL
2, 1
3, 2
经理有两列。
ORG_ID, MANAGER
1, John Doe
2, Jane Doe
3, NULL
我正在尝试创建一个递归查询，该查询将为某个组织单位找到第一个非空经理。
I'm trying to create a recursive query that will find the first non-null manager for a certain organizational unit.
基本上，如果今天我查询经理ORG_ID = 3，我将得到NULL。
Basically if I do a query today for the manager for ORG_ID=3 I will get NULL.
SELECT MANAGER FROM ORG_MANAGERS WHERE ORG_ID = '3'
我希望查询使用ORG_PARENTS表要获取ORG_ID = 3的父对象，在这种情况下，获取 2并针对ORG_ID = 2的ORG_MANAGERS表重复查询，并在此示例中返回 Jane Doe。
I want the query to use the ORG_PARENTS table to get the parent for ORG_ID=3, in this case get "2" and repeat the query against the ORG_MANAGERS table with ORG_ID=2 and return in this example "Jane Doe".
如果查询也返回NULL，我想使用ORG_ID = 2的父级（即ORG_ID = 1等等）重复该过程。
In case the query also returns NULL I want to repeat the process with the parent of ORG_ID=2, i.e. ORG_ID=1 and so on.
我的CTE尝试到目前为止失败了，一个例子是这样：
My CTE attempts so far have failed, one example is this:
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已用尽。
Msg 530, Level 16, State 1, Line 4 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
MANAGER ORG_ID ORG_PARENT
NULL 3 2
推荐答案
您需要保持原始状态您开始使用的ID。试试这个：
You need to keep track of the original ID you start with. Try this:
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的列；这是不必要的，并且使维护变得烦人。
Don't predefine the columns of a CTE; it's not necessary, and makes maintenance annoying.
使用递归CTE时，始终要保留一个计数器，这样就可以限制递归程度，并且可以跟踪自己的深度。
With recursive CTE, always keep a counter, so you can limit the recursiveness, and you can keep track how deep you are.
编辑：
顺便说一句，如果您想要第一个不是null的管理器，则可以这样做（例如
By the way, if you want the first not null manager, you can do for example (there are many ways) this:
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)
这篇关于具有三个表的递归CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
