26-进阶SQL-递归查询（with recursive）-526互联

MySQL with Recursive是一种基于递归思想的MySQL查询方式，可以实现对数据的递归查询和处理，返回符合条件的数据，在MySQL 8.0版本中，该功能被正式引入。

MySQL with Recursive有什么作用

MySQL with Recursive的作用是基于一组初始数据，进行递归查询，返回符合条件的数据集。

这种递归查询方式可以应用在很多场景下，比如对于树形结构、层级结构的数据处理，以及对数据进行分类汇总等。

MySQL with Recursive的语法：

WITH RECURSIVE cte_name (column_list) AS (
    SELECT initial_query_result
    UNION [ALL]
    SELECT recursive_query
    FROM cte_name
    WHERE condition
)
SELECT * FROM cte_name;

WITH RECURSIVE 关键字：表示要使用递归查询的方式处理数据。

cte_name：给这个临时的递归表取个名字，可以在初始查询和递归查询中引用。

column_list：表示cte_name查询表中包含的列名，列名之间用逗号分隔。

initial_query_result：表示初始的查询结果，应该与column_list中的列名对应。

UNION：表示将两个查询结果集进行联合，使用UNION ALL则表示保留重复数据。

recursive_query：表示递归查询语句，应当与column_list中的列名对应。

condition：表示递归查询的终止条件，需要使用cte_name中的列进行判断。

SELECT * FROM cte_name：

表示最终返回的查询结果集，可以通过cte_name查询临时递归表中的列名进行指定。

案例：以山东济南的行政区划作为示例的，创建地区表（熟悉递归查询）

-- 创建表
DROP TABLE IF EXISTS `sys_region`;
CREATE TABLE sys_region  (
  id int NOT NULL AUTO_INCREMENT COMMENT '地区主键编号',
  name varchar(50) COMMENT '地区名称',
  short_name varchar(50) COMMENT '简称',
  code varchar(50) COMMENT '行政地区编号',
  parent_code varchar(50) COMMENT '父id',
  level int(2)  COMMENT '1级：省、直辖市、自治区\r\n2级：地级市\r\n3级：市辖区、县（旗）、县级市、自治县（自治旗）、特区、林区\r\n4级：镇、乡、民族乡、县辖区、街道\r\n5级：村、居委会',
  flag int(1) COMMENT '0:正常 1废弃',
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 182 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '地区表' ROW_FORMAT = Dynamic;

-- 插入数据
INSERT INTO sys_region 
VALUES 
(1, "山东省", "鲁", "370000000000", NULL, 1, 0),
(2, "济南市", "济南", "370100000000", "370000000000", 2, 0),
(3, "市辖区", "市辖区", "370101000000", "370100000000", 3, 0),
(4, "历下区", "历下区", "370102000000", "370100000000", 3, 0),
(5, "市中区", "市中区", "370103000000", "370100000000", 3, 0),
(6, "槐荫区", "槐荫区", "370104000000", "370100000000", 3, 0),
(7, "天桥区", "天桥区", "370105000000", "370100000000", 3, 0),
(8, "历城区", "历城区", "370112000000", "370100000000", 3, 0),
(9, "长清区", "长清区", "370113000000", "370100000000", 3, 0),
(10, "章丘区", "章丘区", "370114000000", "370100000000", 3, 0),
(11, "济阳区", "济阳区", "370115000000", "370100000000", 3, 0),
(12, "莱芜区", "莱芜区", "370116000000", "370100000000", 3, 0),
(13, "钢城区", "钢城区", "370117000000", "370100000000", 3, 0),
(14, "平阴县", "平阴县", "370124000000", "370100000000", 3, 0),
(15, "商河县", "商河县", "370126000000", "370100000000", 3, 0),
(16, "济南高新技术产业开发区", "高新区", "370171000000", "370100000000", 3, 0),
(17, "解放路街道", "解放路街道", "370102001000", "370102000000", 4, 0),
(18, "千佛山街道", "千佛山街道", "370102002000", "370102000000", 4, 0),
(19, "趵突泉街道", "趵突泉街道", "370102003000", "370102000000", 4, 0),
(20, "泉城路街道", "泉城路街道", "370102004000", "370102000000", 4, 0),
(21, "大明湖街道", "大明湖街道", "370102005000", "370102000000", 4, 0),
(22, "东关街道", "东关街道", "370102006000", "370102000000", 4, 0),
(23, "文东街道", "文东街道", "370102007000", "370102000000", 4, 0),
(24, "建新街道", "建新街道", "370102008000", "370102000000", 4, 0),
(25, "甸柳街道", "甸柳街道", "370102009000", "370102000000", 4, 0),
(26, "燕山街道", "燕山街道", "370102010000", "370102000000", 4, 0),
(27, "姚家街道", "姚家街道", "370102011000", "370102000000", 4, 0),
(28, "龙洞街道", "龙洞街道", "370102012000", "370102000000", 4, 0),
(29, "智远街道", "智远街道", "370102013000", "370102000000", 4, 0),
(30, "大观园街道", "大观园街道", "370103002000", "370103000000", 4, 0),
(31, "杆石桥街道", "杆石桥街道", "370103003000", "370103000000", 4, 0),
(32, "四里村街道", "四里村街道", "370103004000", "370103000000", 4, 0),
(33, "魏家庄街道", "魏家庄街道", "370103006000", "370103000000", 4, 0),
(34, "二七街道", "二七街道", "370103008000", "370103000000", 4, 0),
(35, "七里山街道", "七里山街道", "370103009000", "370103000000", 4, 0),
(36, "六里山街道", "六里山街道", "370103010000", "370103000000", 4, 0),
(37, "舜玉路街道", "舜玉路街道", "370103012000", "370103000000", 4, 0),
(38, "泺源街道", "泺源街道", "370103014000", "370103000000", 4, 0),
(39, "王官庄街道", "王官庄街道", "370103015000", "370103000000", 4, 0),
(40, "舜耕街道", "舜耕街道", "370103016000", "370103000000", 4, 0),
(41, "白马山街道", "白马山街道", "370103017000", "370103000000", 4, 0),
(42, "七贤街道", "七贤街道", "370103018000", "370103000000", 4, 0),
(43, "十六里河街道", "十六里河街道", "370103019000", "370103000000", 4, 0),
(44, "兴隆街道", "兴隆街道", "370103020000", "370103000000", 4, 0),
(45, "党家街道", "党家街道", "370103021000", "370103000000", 4, 0),
(46, "陡沟街道", "陡沟街道", "370103022000", "370103000000", 4, 0),
(47, "振兴街街道", "振兴街街道", "370104001000", "370104000000", 4, 0),
(48, "中大槐树街道", "中大槐树街道", "370104002000", "370104000000", 4, 0),
(49, "道德街街道", "道德街街道", "370104003000", "370104000000", 4, 0),
(50, "西市场街道", "西市场街道", "370104004000", "370104000000", 4, 0),
(51, "五里沟街道", "五里沟街道", "370104005000", "370104000000", 4, 0),
(52, "营市街街道", "营市街街道", "370104006000", "370104000000", 4, 0),
(53, "青年公园街道", "青年公园街道", "370104007000", "370104000000", 4, 0),
(54, "南辛庄街道", "南辛庄街道", "370104008000", "370104000000", 4, 0),
(55, "段店北路街道", "段店北路街道", "370104009000", "370104000000", 4, 0),
(56, "张庄路街道", "张庄路街道", "370104010000", "370104000000", 4, 0),
(57, "匡山街道", "匡山街道", "370104011000", "370104000000", 4, 0),
(58, "美里湖街道", "美里湖街道", "370104012000", "370104000000", 4, 0),
(59, "腊山街道", "腊山街道", "370104013000", "370104000000", 4, 0),
(60, "兴福街道", "兴福街道", "370104014000", "370104000000", 4, 0),
(61, "玉清湖街道", "玉清湖街道", "370104015000", "370104000000", 4, 0),
(62, "吴家堡街道", "吴家堡街道", "370104016000", "370104000000", 4, 0),
(63, "无影山街道", "无影山街道", "370105001000", "370105000000", 4, 0),
(64, "天桥东街街道", "天桥东街街道", "370105003000", "370105000000", 4, 0),
(65, "北村街道", "北村街道", "370105004000", "370105000000", 4, 0),
(66, "南村街道", "南村街道", "370105005000", "370105000000", 4, 0),
(67, "堤口路街道", "堤口路街道", "370105006000", "370105000000", 4, 0),
(68, "北坦街道", "北坦街道", "370105007000", "370105000000", 4, 0),
(69, "制锦市街道", "制锦市街道", "370105009000", "370105000000", 4, 0),
(70, "宝华街道", "宝华街道", "370105010000", "370105000000", 4, 0),
(71, "官扎营街道", "官扎营街道", "370105011000", "370105000000", 4, 0),
(72, "纬北路街道", "纬北路街道", "370105012000", "370105000000", 4, 0),
(73, "药山街道", "药山街道", "370105013000", "370105000000", 4, 0),
(74, "北园街道", "北园街道", "370105014000", "370105000000", 4, 0),
(75, "泺口街道", "泺口街道", "370105015000", "370105000000", 4, 0),
(76, "桑梓店街道", "桑梓店街道", "370105016000", "370105000000", 4, 0),
(77, "大桥街道", "大桥街道", "370105017000", "370105000000", 4, 0),
(78, "山大路街道", "山大路街道", "370112001000", "370112000000", 4, 0),
(79, "洪家楼街道", "洪家楼街道", "370112002000", "370112000000", 4, 0),
(80, "东风街道", "东风街道", "370112003000", "370112000000", 4, 0),
(81, "全福街道", "全福街道", "370112004000", "370112000000", 4, 0),
(82, "华山街道", "华山街道", "370112007000", "370112000000", 4, 0),
(83, "荷花路街道", "荷花路街道", "370112008000", "370112000000", 4, 0),
(84, "王舍人街道", "王舍人街道", "370112009000", "370112000000", 4, 0),
(85, "鲍山街道", "鲍山街道", "370112010000", "370112000000", 4, 0),
(86, "郭店街道", "郭店街道", "370112011000", "370112000000", 4, 0),
(87, "唐冶街道", "唐冶街道", "370112012000", "370112000000", 4, 0),
(88, "港沟街道", "港沟街道", "370112013000", "370112000000", 4, 0),
(89, "董家街道", "董家街道", "370112016000", "370112000000", 4, 0),
(90, "彩石街道", "彩石街道", "370112017000", "370112000000", 4, 0),
(91, "仲宫街道", "仲宫街道", "370112018000", "370112000000", 4, 0),
(92, "柳埠街道", "柳埠街道", "370112019000", "370112000000", 4, 0),
(93, "唐王街道", "唐王街道", "370112020000", "370112000000", 4, 0),
(94, "西营街道", "西营街道", "370112021000", "370112000000", 4, 0),
(95, "文昌街道", "文昌街道", "370113001000", "370113000000", 4, 0),
(96, "崮云湖街道", "崮云湖街道", "370113002000", "370113000000", 4, 0),
(97, "平安街道", "平安街道", "370113003000", "370113000000", 4, 0),
(98, "五峰山街道", "五峰山街道", "370113004000", "370113000000", 4, 0),
(99, "归德街道", "归德街道", "370113005000", "370113000000", 4, 0),
(100, "张夏街道", "张夏街道", "370113006000", "370113000000", 4, 0),
(101, "万德街道", "万德街道", "370113007000", "370113000000", 4, 0),
(102, "孝里镇", "孝里镇", "370113102000", "370113000000", 4, 0),
(103, "马山镇", "马山镇", "370113107000", "370113000000", 4, 0),
(104, "双泉镇", "双泉镇", "370113108000", "370113000000", 4, 0),
(105, "明水街道", "明水街道", "370114001000", "370114000000", 4, 0),
(106, "双山街道", "双山街道", "370114002000", "370114000000", 4, 0),
(107, "枣园街道", "枣园街道", "370114003000", "370114000000", 4, 0),
(108, "龙山街道", "龙山街道", "370114004000", "370114000000", 4, 0),
(109, "埠村街道", "埠村街道", "370114005000", "370114000000", 4, 0),
(110, "圣井街道", "圣井街道", "370114006000", "370114000000", 4, 0),
(111, "普集街道", "普集街道", "370114007000", "370114000000", 4, 0),
(112, "绣惠街道", "绣惠街道", "370114008000", "370114000000", 4, 0),
(113, "相公庄街道", "相公庄街道", "370114009000", "370114000000", 4, 0),
(114, "文祖街道", "文祖街道", "370114010000", "370114000000", 4, 0),
(115, "官庄街道", "官庄街道", "370114011000", "370114000000", 4, 0),
(116, "高官寨街道", "高官寨街道", "370114012000", "370114000000", 4, 0),
(117, "白云湖街道", "白云湖街道", "370114013000", "370114000000", 4, 0),
(118, "宁家埠街道", "宁家埠街道", "370114014000", "370114000000", 4, 0),
(119, "曹范街道", "曹范街道", "370114015000", "370114000000", 4, 0),
(120, "***镇", "***镇", "370114100000", "370114000000", 4, 0),
(121, "垛庄镇", "垛庄镇", "370114101000", "370114000000", 4, 0),
(122, "黄河镇", "黄河镇", "370114102000", "370114000000", 4, 0),
(123, "济阳街道", "济阳街道", "370115001000", "370115000000", 4, 0),
(124, "济北街道", "济北街道", "370115002000", "370115000000", 4, 0),
(125, "崔寨街道", "崔寨街道", "370115003000", "370115000000", 4, 0),
(126, "孙耿街道", "孙耿街道", "370115004000", "370115000000", 4, 0),
(127, "回河街道", "回河街道", "370115005000", "370115000000", 4, 0),
(128, "太平街道", "太平街道", "370115006000", "370115000000", 4, 0),
(129, "垛石镇", "垛石镇", "370115101000", "370115000000", 4, 0),
(130, "曲堤镇", "曲堤镇", "370115103000", "370115000000", 4, 0),
(131, "仁风镇", "仁风镇", "370115104000", "370115000000", 4, 0),
(132, "新市镇", "新市镇", "370115110000", "370115000000", 4, 0),
(133, "凤城街道", "凤城街道", "370116001000", "370116000000", 4, 0),
(134, "张家洼街道", "张家洼街道", "370116002000", "370116000000", 4, 0),
(135, "高庄街道", "高庄街道", "370116003000", "370116000000", 4, 0),
(136, "鹏泉街道", "鹏泉街道", "370116004000", "370116000000", 4, 0),
(137, "口镇", "口镇", "370116100000", "370116000000", 4, 0),
(138, "羊里镇", "羊里镇", "370116101000", "370116000000", 4, 0),
(139, "方下镇", "方下镇", "370116102000", "370116000000", 4, 0),
(140, "牛泉镇", "牛泉镇", "370116103000", "370116000000", 4, 0),
(141, "苗山镇", "苗山镇", "370116104000", "370116000000", 4, 0),
(142, "雪野镇", "雪野镇", "370116105000", "370116000000", 4, 0),
(143, "大王庄镇", "大王庄镇", "370116106000", "370116000000", 4, 0),
(144, "寨里镇", "寨里镇", "370116107000", "370116000000", 4, 0),
(145, "杨庄镇", "杨庄镇", "370116108000", "370116000000", 4, 0),
(146, "茶业口镇", "茶业口镇", "370116109000", "370116000000", 4, 0),
(147, "和庄镇", "和庄镇", "370116110000", "370116000000", 4, 0),
(148, "艾山街道", "艾山街道", "370117001000", "370117000000", 4, 0),
(149, "里辛街道", "里辛街道", "370117002000", "370117000000", 4, 0),
(150, "汶源街道", "汶源街道", "370117003000", "370117000000", 4, 0),
(151, "颜庄镇", "颜庄镇", "370117100000", "370117000000", 4, 0),
(152, "辛庄镇", "辛庄镇", "370117103000", "370117000000", 4, 0),
(153, "棋山国家森林公园", "棋山国家森林公园", "370117400000", "370117000000", 4, 0),
(154, "高新技术开发区", "高新技术开发区", "370117401000", "370117000000", 4, 0),
(155, "榆山街道", "榆山街道", "370124001000", "370124000000", 4, 0),
(156, "锦水街道", "锦水街道", "370124002000", "370124000000", 4, 0),
(157, "东阿镇", "东阿镇", "370124102000", "370124000000", 4, 0),
(158, "孝直镇", "孝直镇", "370124103000", "370124000000", 4, 0),
(159, "孔村镇", "孔村镇", "370124104000", "370124000000", 4, 0),
(160, "洪范池镇", "洪范池镇", "370124105000", "370124000000", 4, 0),
(161, "玫瑰镇", "玫瑰镇", "370124106000", "370124000000", 4, 0),
(162, "安城镇", "安城镇", "370124107000", "370124000000", 4, 0),
(163, "许商街道", "许商街道", "370126001000", "370126000000", 4, 0),
(164, "殷巷镇", "殷巷镇", "370126101000", "370126000000", 4, 0),
(165, "怀仁镇", "怀仁镇", "370126102000", "370126000000", 4, 0),
(166, "龙桑寺镇", "龙桑寺镇", "370126104000", "370126000000", 4, 0),
(167, "郑路镇", "郑路镇", "370126105000", "370126000000", 4, 0),
(168, "贾庄镇", "贾庄镇", "370126106000", "370126000000", 4, 0),
(169, "玉皇庙镇", "玉皇庙镇", "370126107000", "370126000000", 4, 0),
(170, "白桥镇", "白桥镇", "370126108000", "370126000000", 4, 0),
(171, "孙集镇", "孙集镇", "370126109000", "370126000000", 4, 0),
(172, "韩庙镇", "韩庙镇", "370126110000", "370126000000", 4, 0),
(173, "沙河镇", "沙河镇", "370126111000", "370126000000", 4, 0),
(174, "张坊镇", "张坊镇", "370126112000", "370126000000", 4, 0),
(175, "舜华路街道", "舜华路街道", "370171001000", "370171000000", 4, 0),
(176, "孙村街道", "孙村街道", "370171002000", "370171000000", 4, 0),
(177, "巨野河街道", "巨野河街道", "370171003000", "370171000000", 4, 0),
(178, "遥墙街道", "遥墙街道", "370171004000", "370171000000", 4, 0),
(179, "临港街道", "临港街道", "370171005000", "370171000000", 4, 0),
(180, "创新谷街道办事处", "创新谷街道办事处", "370171400000", "370171000000", 4, 0),
(181, "章锦街道", "章锦街道", "370171401000", "370171000000", 4, 0);

第一种方式，使用 WITH RECURSIVE 实现递归（只适用于MySQL8.0及以上版本）

1）查询子节点，含自己

-- 查询子节点  含自己
WITH RECURSIVE recursion (id, name, short_name, code, parent_code, level, flag) AS (
    SELECT T1.id, T1.name, T1.short_name, T1.code, T1.parent_code, T1.level, T1.flag  
    FROM sys_region T1
    WHERE T1.code='370000000000'
    UNION ALL
    SELECT T2.id, T2.name, T2.short_name, T2.code, T2.parent_code, T2.level, T2.flag
    FROM sys_region T2, recursion T3
    WHERE T2.parent_code=T3.code
)
SELECT T.id, T.name, T.short_name, T.code, T.parent_code, T.level, T.flag FROM recursion T;

recursive

with

SQL

recursive with sql 26

sql 26

引擎sql 26

server nolock with sql