Mysql 使用WITH查询每个类别对应的父类、类别层级深度（总共有几层）、类别层级路径。
with recursive cte as (select category_id, category_name, parent, category_name as parent_name -- 查询每个类别的父类, 0 as depth -- 查询类别层级深度, cast(category_id as char(200)) as path -- 查询类别层级路径from categorywhere parent is nullunion allselect c.category_id, c.category_name, c.parent, cte.category_name as parent_name -- 查询每个类别的父类, cte.depth + 1 as depth -- 查询类别层级深度, concat(cte.path, '->', c.category_id) as path -- 查询类别层级路径from category as cinner join cteon c.parent = cte.category_id)
select * from cte;
+-------------+----------------------+--------+----------------------+-------+------------+
| category_id | category_name | parent | parent_name | depth | path |
+-------------+----------------------+--------+----------------------+-------+------------+
| 1 | ELECTRONICS | NULL | ELECTRONICS | 0 | 1 |
| 2 | TELEVISIONS | 1 | ELECTRONICS | 1 | 1->2 |
| 6 | PORTABLE ELECTRONICS | 1 | ELECTRONICS | 1 | 1->6 |
| 3 | TUBE | 2 | TELEVISIONS | 2 | 1->2->3 |
| 4 | LCD | 2 | TELEVISIONS | 2 | 1->2->4 |
| 5 | PLASMA | 2 | TELEVISIONS | 2 | 1->2->5 |
| 7 | MP3 PLAYERS | 6 | PORTABLE ELECTRONICS | 2 | 1->6->7 |
| 9 | CD PLAYERS | 6 | PORTABLE ELECTRONICS | 2 | 1->6->9 |
| 10 | 2 WAY RADIOS | 6 | PORTABLE ELECTRONICS | 2 | 1->6->10 |
| 8 | FLASH | 7 | MP3 PLAYERS | 3 | 1->6->7->8 |
+-------------+----------------------+--------+----------------------+-------+------------+
