初始表 category:
目标结果:
首先将表进行联结:
select c.`类目id`,c.`父类目id`,c1.`类目id`,c1.`父类目id`,c2.`类目id`,c2.`父类目id`
from category c
left join category c1 on c.`父类目id` = c1.`类目id`
left join category c2 on c1.`父类目id` = c2.`类目id`
结果:
SQL:
select if(c2.`类目id` is not null, c.`类目id`, null) as 三级类目,
if(c2.`类目id` is not null, c.`父类目id`, null) as 二级类目,
c2.`类目id` as 一级类目,
c2.`父类目id` as 根目录
from category c
left join category c1 on c.`父类目id` = c1.`类目id`
left join category c2 on c1.`父类目id` = c2.`类目id`
where if(c2.`类目id` is not null, c.`类目id`, null) is not null
group by 三级类目
结果: