mysql - Hierarchical Query 将中级类别归为顶级

Question

我有一个包含 3 级分层数据的表，但我的查询对级别的分类不正确。

我的查询:

SELECT  t1.name AS lev1, t2.name as lev2, t3.name as lev3
FROM sds_test AS t1
LEFT JOIN sds_test AS t2 ON t2.parent_id = t1.ID
LEFT JOIN sds_test AS t3 ON t3.parent_id = t2.ID;

Museum 和 Arts schools 类别获得了额外的一行，因为它们是顶级类别，这是我想要消除的。如果我把 level 2 设置为不为 NULL，它会弄乱那些只有一个顶级的类别，例如航空

Desired Result Museum and Arts School 不应该是顶级类别，也不应该为它们单独一行。

我不确定如何解决这个问题，因为 CTE 也让我得到了类似的结果。我试过用 case

当 parent_id = NULL then 的情况这又没有给我想要的结果。

重新生成表的 SQL 查询:

create table sds_test (id int, name varchar(30), parent_id int);
insert into sds_test values(1, 'Educational Institute', NULL);
insert into sds_test values(2, 'Speciality Schools', 1);
insert into sds_test values(3, 'Arts School', 2);

insert into sds_test values(4, 'Restraunts', NULL);
insert into sds_test values(5, 'Asian Restraunts', 4);
insert into sds_test values(6, 'malasian Rest', 5);

insert into sds_test values(7, 'Recreational', NULL);
insert into sds_test values(8, 'Museum', 7);

insert into sds_test values(9, 'Aviation', NULL);

Answer 1

我觉得你们很亲密:

SELECT  t1.name AS lev1, t2.name as lev2, t3.name as lev3
FROM sds_test AS t1
LEFT JOIN sds_test AS t2 ON t2.parent_id = t1.ID
LEFT JOIN sds_test AS t3 ON t3.parent_id = t2.ID
WHERE t1.parent_id IS NULL

结果:

lev1                    lev2                lev3    
Educational Institute   Speciality Schools  Arts School
Restraunts              Asian Restraunts    malasian Rest
Recreational            Museum              NULL
Aviation                NULL                NULL 

我所做的只是添加条件，即 t1 中的项目不应有父项。

这显然与期望的输出不匹配。我想知道为什么航空不应该在那里？