sql - 您如何在递归CTE中创建UNION？

Question

这里有一个标准的层次表：

CREATE TABLE GroupMembers (
   group_id    VARCHAR,
   member_id   VARCHAR
);

INSERT INTO GroupMembers VALUES
    ('1', '10'),
    ('1', '11'),
    ('10','100'),
    ('10','101'),
    ('20','200'),
    ('20','201');

现在，要获取组“ 1”的所有成员，请执行以下操作：

WITH RECURSIVE members(id) AS (
   VALUES('1')
   UNION
   SELECT gm.member_id
   FROM GroupMembers gm
   INNER JOIN members m ON gm.group_id=m.id
)
SELECT * FROM members;

结果为：“ 1”，“ 10”，“ 11”，“ 100”，“ 101”。

现在有一个新问题：我为id取别名

CREATE TABLE Aliases (
   member_id  VARCHAR,
   alias_id   VARCHAR
);

INSERT INTO Aliases(member_id, alias_id) VALUES
    ('11', '20');

现在我想获得所有成员。由于“ 11”是“ 20”的别名，因此结果应为：“ 1”，“ 10”，“ 11”，“ 100”，“ 101”，“ 200”和“ 201”。

我尝试了以下查询：

WITH RECURSIVE members(id) AS (
   VALUES('1')
   UNION
   SELECT gm.member_id
   FROM GroupMembers gm
   INNER JOIN members m ON gm.group_id=m.id
   UNION
   SELECT a.alias_id
   FROM Aliases
   INNER JOIN members m ON gm.group_id=m.id
)
SELECT * FROM members;

但我收到错误 circular reference: members

Answer 1

这个怎么样：

WITH RECURSIVE members(id) AS (
   VALUES('1')
   UNION
   SELECT groupWithAliases.member_id
   FROM 
   ( SELECT group_id, member_id FROM GroupMembers 
     UNION
     SELECT alias_id AS group_id, member_id
     FROM Aliases ) groupWithAliases
   INNER JOIN members m ON groupWithAliases.group_id=m.id
)
SELECT * FROM members;

请注意，我在构造 groupWithAliases子查询时使用UNION来消除重复项