mysql 中with的用法（2）

with递归练习主要用于表里面包含父节点id之类的

查询出对应的省份和市。

建表

CREATE TABLE tb(id VARCHAR(3), pid VARCHAR(3), name VARCHAR(64));
 
INSERT INTO tb VALUES('002', 0, '浙江省');
INSERT INTO tb VALUES('001', 0, '广东省');
INSERT INTO tb VALUES('003', '002', '衢州市');
INSERT INTO tb VALUES('004', '002', '杭州市');
INSERT INTO tb VALUES('005', '002', '湖州市');
INSERT INTO tb VALUES('006', '002', '嘉兴市');
INSERT INTO tb VALUES('007', '002', '宁波市');
INSERT INTO tb VALUES('008', '002', '绍兴市');
INSERT INTO tb VALUES('009', '002', '台州市');
INSERT INTO tb VALUES('010', '002', '温州市');
INSERT INTO tb VALUES('011', '002', '丽水市');
INSERT INTO tb VALUES('012', '002', '金华市');
INSERT INTO tb VALUES('013', '002', '舟山市');
INSERT INTO tb VALUES('014', '004', '上城区');
INSERT INTO tb VALUES('015', '004', '下城区');
INSERT INTO tb VALUES('016', '004', '拱墅区');
INSERT INTO tb VALUES('017', '004', '余杭区');
INSERT INTO tb VALUES('018', '011', '金东区');
INSERT INTO tb VALUES('019', '001', '广州市');
INSERT INTO tb VALUES('020', '001', '深圳市')
 

解决办法：

WITH RECURSIVE cte AS (
 SELECT id,name FROM tb WHERE id in ('001','002')
 UNION ALL
 SELECT k.id, CONCAT(c.name,'->',k.name) AS name FROM tb k INNER JOIN cte c ON c.id = k.pid

) 
SELECT * FROM cte  ORDER BY NAME;