回答：WITH RECURSIVE region_hierarchy AS ( -- 基础查询，获取省级单位 SELECT id, pid, name, CAST(name AS CHAR(255)) AS path FROM tb WHERE pid = '0' UNION ALL -- 递归查询，获取下一层级的单位 SELECT tb.id, tb.pid, tb.name, CONCAT(rh.path, ' > ', tb.name) FROM tb JOIN region_hierarchy rh ON tb.pid = rh.id)SELECT -- 根据层级拆分path，获取省、市、区的名称 SUBSTRING_INDEX(SUBSTRING_INDEX(path, ' > ', 1), ' > ', -1) AS name_y, CASE WHEN COUNT(SUBSTRING_INDEX(path, ' > ', -1)) > 1 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(path, ' > ', 2), ' > ', -1) ELSE NULL END AS name_s, CASE WHEN COUNT(SUBSTRING_INDEX(path, ' > ', -1)) > 2 THEN SUBSTRING_INDEX(path, ' > ', -1) ELSE NULL END AS name_qFROM region_hierarchyGROUP BY pathORDER BY path;
