MySQL8查询层次结构的全血统(祖先+后代)

Question

MySQL8允许使用with语法查询层次结构。例如，查询一个项的所有祖先。相反，也可以递归地查询所有后代。

​

​

我正在寻找一个将这两个方面结合在一起的查询:对所有后代、祖先和最优的项self的查询(参见包含的图形，所有彩色项)。实际上，你也可以说，所有的物品没有所有祖先的兄弟姐妹。我想我们可以说“血统”。这两个结果是可能的，但是我需要一个没有联合的解决方案。

有可能吗？我欢迎任何办法。

概述一下，我的工会尝试。注意，目前有一个错误，一些parent_id是错误的；我正在做这个)

(with recursive cte as (
    SELECT id, parent_id
    from categories
    where id = 'C2'
    union all
    select t.parent_id, cte.id
    from cte
             inner join categories t on t.id = cte.parent_id
    where cte.id is not null
)
 select id, parent_id
 from cte
 where cte.id is not null)

union

(with recursive cte as (
    SELECT id, parent_id
    from categories
    where id = 'C2'
    union all
    select t.id, cte.parent_id
    from cte
             inner join categories t on t.parent_id = cte.id
)
 select id, parent_id
 from cte);

以及给定图表的数据。

INSERT INTO categories (id, parent_id) VALUES ('A1', null);
INSERT INTO categories (id, parent_id) VALUES ('B1', 'A1');
INSERT INTO categories (id, parent_id) VALUES ('B2', 'A1');
INSERT INTO categories (id, parent_id) VALUES ('B3', 'A1');
INSERT INTO categories (id, parent_id) VALUES ('C1', 'B2');
INSERT INTO categories (id, parent_id) VALUES ('C2', 'B2');
INSERT INTO categories (id, parent_id) VALUES ('D1', 'C1');
INSERT INTO categories (id, parent_id) VALUES ('D2', 'C1');
INSERT INTO categories (id, parent_id) VALUES ('D3', 'C2');
INSERT INTO categories (id, parent_id) VALUES ('D4', 'C2');
INSERT INTO categories (id, parent_id) VALUES ('D5', 'C2');
INSERT INTO categories (id, parent_id) VALUES ('E1', 'D5');

预期输出

id,parent_id
C2,B2
A1,null
B2,A1
D3,C2
D4,C2
D5,C2
E1,D5

Answer 1

如果在ON子句中同时包括祖先和后代的条件，则可以使用1个递归CTE来完成此操作。

您将需要另一个列type，该列指示每一行是针对祖先还是后代：

WITH RECURSIVE cte AS (
    SELECT id, parent_id, 0 type
    FROM categories
    WHERE id = 'C2'
    UNION ALL
    SELECT t.id, t.parent_id,
           CASE WHEN c.parent_id = t.id THEN -1 ELSE 1 END
    FROM categories t INNER JOIN cte c
    ON c.parent_id = t.id OR c.id = t.parent_id
    WHERE (type = 0) 
       OR (c.parent_id = t.id AND type = -1) 
       OR (c.id = t.parent_id AND type = 1)
)
SELECT id, parent_id FROM cte

见演示。