CTE表和CTE (MariaDB)

Question

这是我第一次在这里提出我的问题，所以我希望得到一个足够清晰的答案。

我有收藏，需要在它们之间创建一种关系。每个集合都应该是任何其他集合(及其childs)的子集。

定义的关系示例(带有ID)如下：

2
|-3
|-4

5
|-3

4
|-5

现在，当检索集合2的树时，我需要获取(期望)：

2
|-3
|-4
  |-5
    |-3

然而，得到了：

2
|-3
|-4
  |-5

测试表：

CREATE TABLE collection_bom (
    id INT,
    collection_id varchar(20),
    reference_id varchar(20)
);

-- definition 2-3-4 relationship
INSERT INTO collection_bom(id,collection_id,reference_id) VALUES (1,2,NULL);
INSERT INTO collection_bom(id,collection_id,reference_id) VALUES (2,3,1);
INSERT INTO collection_bom(id,collection_id,reference_id) VALUES (3,4,2);
-- definition of 5-3 relationship
INSERT INTO collection_bom(id,collection_id,reference_id) VALUES (4,5,NULL);
INSERT INTO collection_bom(id,collection_id,reference_id) VALUES (5,3,4);
-- definition of 4-5 relationship
INSERT INTO collection_bom(id,collection_id,reference_id) VALUES (6,5,3);

查询：

with recursive cte as (
      select t.*
      from collection_bom t
      where reference_id IS NULL AND collection_id = 2
      union all
      select t.*
      from cte join
           collection_bom t
           on cte.id = t.reference_id
     )
select *
from cte;

Result:
id  collection_id   reference_id
1   2               null
2   3               1
3   4               2
6   5               3

在结果中，我还需要将collection_id 3作为5的孩子。

现在我意识到这可能不是一个好的方法。感谢任何人的帮助！

Fiddle

Answer 1

在插入部分中有一个语义问题；5-3关系的定义从头开始创建一棵新树(具有空引用)，该树独立于原始树。因此，您在第二个树中定义的"5“与第一个树中的其他"5”不同。在4-5关系的定义中，您没有使用空引用，而是将其链接到第一个表中的引用，因此它工作得很好。

此插入部分可以执行您想要的操作：

-- definition 2-3-4 relationship
INSERT INTO collection_bom(id,collection_id,reference_id) VALUES (1,2,NULL);
INSERT INTO collection_bom(id,collection_id,reference_id) VALUES (2,3,1);
INSERT INTO collection_bom(id,collection_id,reference_id) VALUES (3,4,1);
-- definition of 4-5 relationship
INSERT INTO collection_bom(id,collection_id,reference_id) VALUES (4,5,3);
-- definition of 5-3 relationship
-- INSERT INTO collection_bom(id,collection_id,reference_id) VALUES (4,5,NULL);
INSERT INTO collection_bom(id,collection_id,reference_id) VALUES (5,3,4);