回答：CREATE TABLE `tb` (
`id` varchar(3) DEFAULT NULL,
`pid` varchar(3) DEFAULT NULL,
`name` varchar(64) DEFAULT NULL)INSERT INTO tb (id, pid, name) VALUES('002', '0', '浙江省');INSERT INTO tb (id, pid, name) VALUES('001', '0', '广东省');INSERT INTO tb (id, pid, name) VALUES('003', '002', '衢州市');INSERT INTO tb (id, pid, name) VALUES('004', '002', '杭州市');INSERT INTO tb (id, pid, name) VALUES('005', '002', '湖州市');INSERT INTO tb (id, pid, name) VALUES('006', '002', '嘉兴市');INSERT INTO tb (id, pid, name) VALUES('007', '002', '宁波市');INSERT INTO tb (id, pid, name) VALUES('008', '002', '绍兴市');INSERT INTO tb (id, pid, name) VALUES('009', '002', '台州市');INSERT INTO tb (id, pid, name) VALUES('010', '002', '温州市');INSERT INTO tb (id, pid, name) VALUES('011', '002', '丽水市');INSERT INTO tb (id, pid, name) VALUES('012', '002', '金华市');INSERT INTO tb (id, pid, name) VALUES('013', '002', '舟山市');INSERT INTO tb (id, pid, name) VALUES('014', '004', '上城区');INSERT INTO tb (id, pid, name) VALUES('015', '004', '下城区');INSERT INTO tb (id, pid, name) VALUES('016', '004', '拱墅区');INSERT INTO tb (id, pid, name) VALUES('017', '004', '余杭区');INSERT INTO tb (id, pid, name) VALUES('018', '011', '金东区');INSERT INTO tb (id, pid, name) VALUES('019', '001', '广州市');INSERT INTO tb (id, pid, name) VALUES('020', '001', '深圳市')二、解决SQL：第一种写法：WITH RECURSIVE location_cte AS (
-- 初始选择基础条件，即pid为0的记录，表示省级行政区域
SELECT
id,
pid,
name,
0 AS level -- 初始层级为0
FROM tb WHERE pid = 0
-- 递归部分：联接递归表达式自身以获取下一级行政区域
UNION ALL
SELECT
tb.id,
tb.pid,
tb.name,
location_cte.level + 1 -- 层级加1
FROM tb JOIN location_cte ON tb.pid = location_cte.id)-- 最终查询：基于递归CTE获取所需的数据SELECT
t1.name AS shen,
t2.name AS shi,
t3.name AS qu
FROM location_cte t1LEFT JOIN location_cte t2 ON t1.id = t2.pid AND t2.pid <> 0LEFT JOIN location_cte t3 ON t2.id = t3.pidWHERE t1.pid = 0ORDER BY t1.id, t2.id, t3.id;第二种写法：SELECT
t1.name AS shen,
t2.name AS shi,
t3.name AS qu
FROM tb t1LEFT JOIN tb t2 ON t1.id = t2.pid AND t2.pid <> 0LEFT JOIN tb t3 ON t2.id = t3.pidWHERE t1.pid = 0ORDER BY t1.id, t2.id, t3.id;
