mysql查询所有父节点名称拼接

转载

mob64ca140f29e5 2024-09-09 08:56:39

文章标签 mysql查询所有父节点名称拼接 connect by start with mysql 向上查 文章分类 MySQL 数据库

oracle connect by 的知识自己去查，直接上mysql代码
 
CREATE TABLE `lzh_test2` (
   `AREA_ID` varchar(64) NOT NULL,
   `AREA_CODE` varchar(64) DEFAULT NULL,
   `FID` varchar(64) DEFAULT NULL,
   `AREANAME` varchar(128) NOT NULL,
   `FULLNAME` varchar(1024) DEFAULT NULL,
   `LEVEL_FLG` char(1) NOT NULL,
   PRIMARY KEY (`AREA_ID`) USING BTREE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO `lzh_test2` VALUES ('0', '86', '-1', '全国', '全国', '0');
 INSERT INTO `lzh_test2` VALUES ('1', '410000', '0', '河南省', '河南省', '1');
 INSERT INTO `lzh_test2` VALUES ('1.1', '410100', '1', '郑州市', '河南省.郑州市', '2');
 INSERT INTO `lzh_test2` VALUES ('1.1.1', '410101', '1.1', '市辖区', '河南省.郑州市.市辖区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.10', '410182', '1.1', '荥阳市', '河南省.郑州市.荥阳市', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.11', '410183', '1.1', '新密市', '河南省.郑州市.新密市', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.12', '410184', '1.1', '新郑市', '河南省.郑州市.新郑市', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.13', '410185', '1.1', '登封市', '河南省.郑州市.登封市', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.14', '410111', '1.1', '东开发区', '河南省.郑州市.东开发区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.15', '410109', '1.1', '西开发区', '河南省.郑州市.西开发区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.16', '410110', '1.1', '经济开发区', '河南省.郑州市.经济开发区','3');
 INSERT INTO `lzh_test2` VALUES ('1.1.17', '410112', '1.1', '航空港区', '河南省.郑州市.航空港区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.18', '410113', '1.1', '郑东新区', '河南省.郑州市.郑东新区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.2', '410102', '1.1', '中原区', '河南省.郑州市.中原区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.3', '410103', '1.1', '二七区', '河南省.郑州市.二七区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.4', '410104', '1.1', '管城回族区', '河南省.郑州市.管城回族区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.5', '410105', '1.1', '金水区', '河南省.郑州市.金水区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.6', '410106', '1.1', '上街区', '河南省.郑州市.上街区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.7', '410108', '1.1', '惠济区', '河南省.郑州市.惠济区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.8', '410122', '1.1', '中牟县', '河南省.郑州市.中牟县', '3');
 INSERT INTO `lzh_test2` VALUES ('1.1.9', '410181', '1.1', '巩义市', '河南省.郑州市.巩义市', '3');
 INSERT INTO `lzh_test2` VALUES ('1.10', '410900', '1', '濮阳市', '河南省.濮阳市', '2');
 INSERT INTO `lzh_test2` VALUES ('1.10.1', '410901', '1.10', '市辖区', '河南省.濮阳市.市辖区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.10.2', '410902', '1.10', '华龙区', '河南省.濮阳市.华龙区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.10.3', '410922', '1.10', '清丰县', '河南省.濮阳市.清丰县', '3');
 INSERT INTO `lzh_test2` VALUES ('1.10.4', '410923', '1.10', '南乐县', '河南省.濮阳市.南乐县', '3');
 INSERT INTO `lzh_test2` VALUES ('1.10.5', '410926', '1.10', '范县', '河南省.濮阳市.范县', '3');
 INSERT INTO `lzh_test2` VALUES ('1.10.6', '410927', '1.10', '台前县', '河南省.濮阳市.台前县', '3');
 INSERT INTO `lzh_test2` VALUES ('1.10.7', '410928', '1.10', '濮阳县', '河南省.濮阳市.濮阳县', '3');
 INSERT INTO `lzh_test2` VALUES ('1.11', '411000', '1', '许昌市', '河南省.许昌市', '2');
 INSERT INTO `lzh_test2` VALUES ('1.11.1', '411001', '1.11', '市辖区', '河南省.许昌市.市辖区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.11.2', '411002', '1.11', '魏都区', '河南省.许昌市.魏都区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.11.3', '411023', '1.11', '许昌县', '河南省.许昌市.许昌县', '3');
 INSERT INTO `lzh_test2` VALUES ('1.11.4', '411024', '1.11', '鄢陵县', '河南省.许昌市.鄢陵县', '3');
 INSERT INTO `lzh_test2` VALUES ('1.11.5', '411025', '1.11', '襄城县', '河南省.许昌市.襄城县', '3');
 INSERT INTO `lzh_test2` VALUES ('1.11.6', '411081', '1.11', '禹州市', '河南省.许昌市.禹州市', '3');
 INSERT INTO `lzh_test2` VALUES ('1.11.7', '411082', '1.11', '长葛市', '河南省.许昌市.长葛市', '3');
 INSERT INTO `lzh_test2` VALUES ('1.11.8', '411088', '1.11', '东城区', '河南省.许昌市.东城区', '3');
 INSERT INTO `lzh_test2` VALUES ('1.11.9', '411089', '1.11', '开发区', '河南省.许昌市.开发区', '3');select * from lzh_test2;
方法一（mysql 8 ，使用通用表表达式 with as 实现）：
-- 向下查
 with recursive t1 as (
 select t0.* from lzh_test2 t0 where t0.area_id='1.1'  -- Anchor member.
  union all
 select t2.* from lzh_test2 t2, t1  -- Recursive member.
  where t2.fid = t1.area_id
 )
 select * from t1 order by area_id;-- 向上查
 with recursive t1 as (
 select * from lzh_test2 t0 where t0.area_id='1.1.3'  -- Anchor member.
  union all
 select t2.* from lzh_test2 t2, t1  -- Recursive member.
  where t2.area_id = t1.fid
 )
 select * from t1 order by area_id; -- 向下查，带level
 with recursive t1 as (
 select 1 lv,t0.* from lzh_test2 t0 where t0.area_id='1.11'  -- Anchor member.
  union all
 select t1.lv+1 lv,t2.* from lzh_test2 t2, t1  -- Recursive member.
  where t2.fid = t1.area_id
 )
 select * from t1 order by area_id;-- 向上查，带level
 with recursive t1 as (
 select 1 lv,t0.* from lzh_test2 t0 where t0.area_id='1.11.3'  -- Anchor member.
  union all
 select t1.lv+1 lv,t2.* from lzh_test2 t2, t1  -- Recursive member.
  where t2.area_id = t1.fid
 )
 select * from t1 order by area_id;方法二（使用变量实现）：
-- 向下查
 SELECT    b.*
 FROM (SELECT @ids AS _ids,
                         (SELECT @ids := GROUP_CONCAT(area_id) FROM lzh_test2 WHERE FIND_IN_SET(fid, @ids)) AS cids
             FROM lzh_test,
                     (SELECT @ids := '1.10') b 
             WHERE @ids IS NOT NULL) id, 
         lzh_test2 b
 WHERE FIND_IN_SET(b.area_id, ID._ids) ORDER BY b.area_id-- 向上查
 SELECT    b.*
 FROM (SELECT @ids AS _ids,
                         (SELECT @ids := GROUP_CONCAT(fid) FROM lzh_test2 WHERE FIND_IN_SET(area_id, @ids)) AS cids
             FROM lzh_test2,
                     (SELECT @ids := '1.1.17') b 
             WHERE @ids IS NOT NULL) id, 
         lzh_test2 b
 WHERE FIND_IN_SET(b.area_id, ID._ids) ORDER BY b.area_id -- 向下查，带level
 SELECT    ID.level, 
                 b.*
 FROM (SELECT @ids AS _ids,
                         (SELECT @ids := GROUP_CONCAT(area_id) FROM lzh_test2 WHERE FIND_IN_SET(fid, @ids)) AS cids, 
                         @l := @l + 1 AS LEVEL 
             FROM lzh_test2,
                     (SELECT @ids := '1.11', @l := 0) b 
             WHERE @ids IS NOT NULL) id, 
         lzh_test2 b
 WHERE FIND_IN_SET(b.area_id, ID._ids) ORDER BY LEVEL, b.area_id-- 向上查，带level
 SELECT    ID.level, 
                 b.*
 FROM (SELECT @ids AS _ids,
                         (SELECT @ids := GROUP_CONCAT(fid) FROM lzh_test2 WHERE FIND_IN_SET(area_id, @ids)) AS cids, 
                         @l := @l + 1 AS LEVEL 
             FROM lzh_test2,
                     (SELECT @ids := '1.1.17', @l := 0) b 
             WHERE @ids IS NOT NULL) id, 
         lzh_test2 b
 WHERE FIND_IN_SET(b.area_id, ID._ids) ORDER BY LEVEL, b.area_id

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。