Mysql 使用WITH查询每个类别对应的父类、类别层级深度（总共有几层）、类别层级路径。

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 category
                       where parent is null
                       union all
                       select 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 c
                                inner join cte
                                           on 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 |
+-------------+----------------------+--------+----------------------+-------+------------+