1.建立依赖表,以商品分类表为例,每个分类都会有自己的下级分类
CREATE TABLE `category` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT, //主键id
`name` varchar(100), //分类名称
`parent_id` int NOT NULL DEFAULT 0 //父分类的id
PRIMARY KEY (`id`) USING BTREE
)
2.添加数据(category)
2.以这些数据为基础,查询每个分类的下级(包括下级,下下级)
//递归
with RECURSIVE category_demo(id,parent_name,last_id,last_name) as (
select
a.id,a.name,b.id,b.name
from category a join category b on a.id = b.parent_id //先获取每个分类的子级
UNION ALL
select
c.id,c.parent_name,d.id,d.name
from category_demo c //递归查询关联获取子级的下一级
join category d on c.last_id = d.parent_id
)
SELECT * FROM category_demo order by id asc,last_id asc
结果: