递归 CTE，包括锚点内的联合和递归表达式

Question

所以我有三个表，具有以下模式，

Users(id, name)

Colleagues(id1, id2)

Friends(id1, id2)

我需要编写一个返回每对 id 的查询，以便可以使用同事和朋友之间的任意数量的连接从id_1访问id_2。 我计算出了一个查询，它使用同事或朋友为我提供了每个连接，但不能同时使用两者。 这就是我尝试在同一 CTE 中使用两个表时想到的：

WITH RECURSIVE Reachable (id_1, id_2)
AS (
SELECT
*
FROM (
SELECT
id,
FRIENDS.id2
FROM
USERS,
FRIENDS
WHERE
FRIENDS.id1 = USERS.id
UNION
SELECT
id,
COLLEAGUES.id2
FROM
USERS,
COLLEAGUES
WHERE
COLLEAGUES.id1 = USERS.id)
UNION
SELECT
*
FROM (
SELECT
REACHABLE.id_1,
FRIENDS.id2
FROM
REACHABLE,
FRIENDS
WHERE
REACHABLE.id_2 = FRIENDS.id1
UNION
SELECT
REACHABLE.id_1,
COLLEAGUES.id2
FROM
REACHABLE,
COLLEAGUES
WHERE
REACHABLE.id_2 = COLLEAGUES.id1));

但是我收到此错误：

Error: near line 1: recursive reference in a subquery: Reachable

这是否意味着我不能/不应该在递归调用中使用子查询？ 是否可以在同一 CTE 中执行此查询？ 如果是这样，我该怎么做？ 提前感谢！

Answer 1

对递归 CTE 的引用不得在子查询中，并且用 UNION (ALL( 分隔的两个部分必须是 WITH 的顶层。

如果此查询的朋友和同事之间没有区别，则只需在执行递归 CTE 之前合并两个表：

WITH RECURSIVE
Connections AS (
SELECT id_1, id_2 FROM Colleagues
UNION ALL 
SELECT id_1, id_2 FROM Friends
),
Reachable(id_1, id_2) AS (
SELECT ...
FROM Users, Connections
...
UNION
...
)
SELECT * FROM Reachable;