mysql递归自身连接_Mysql递归连接

基于这种结构

create table category (id int, mr varchar(10), category_fk int);

create table products (id int, name varchar(100), category_fk int);

insert category values

(1, 'cat1', null),(2, 'cat2', 1),(3, 'cat3', 2),(4, 'cat4', 3),

(5, 'cat5', null),(6, 'cat6', null),(7, 'cat7', 5);

insert products values

(1, 'product A', 4),

(2, 'product B', 3),

(3, 'product C', 5),

(4, 'product D', 7),

(5, 'product E', 7),

(6, 'product F', 1);查询

select p.name, substr(concat(

ifnull(concat('/',c4.mr),''),

ifnull(concat('/',c3.mr),''),

ifnull(concat('/',c2.mr),''),

ifnull(concat('/',c1.mr),'')),2)

from products p

left join category c1 on p.category_fk = c1.id

left join category c2 on c1.category_fk = c2.id

left join category c3 on c2.category_fk = c3.id

left join category c4 on c3.category_fk = c4.id笔记

substr剥离第一个/

concat与联接(父亲优先)的顺序相反

concat'/'和null返回null，更改为''，否则会使主concat为NULL