mysql 递归查询 （MySQL 8.0 ETC 递归查询）

一、数据库数据

 

1、代码

-- ----------------------------
-- Table structure for areas
-- ----------------------------
DROP TABLE IF EXISTS `areas`;
CREATE TABLE `areas` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '行政区名称',
  `pid` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of areas
-- ----------------------------
INSERT INTO `areas` VALUES ('1', '中国', '0');
INSERT INTO `areas` VALUES ('2', '北京市', '1');
INSERT INTO `areas` VALUES ('7', '东城区', '2');
INSERT INTO `areas` VALUES ('8', '西城区', '2');
INSERT INTO `areas` VALUES ('9', '朝阳区', '2');
INSERT INTO `areas` VALUES ('10', '丰台区', '2');
INSERT INTO `areas` VALUES ('11', '石景山区', '2');
INSERT INTO `areas` VALUES ('12', '海淀区', '2');
INSERT INTO `areas` VALUES ('13', '顺义区', '2');
INSERT INTO `areas` VALUES ('14', '通州区', '2');
INSERT INTO `areas` VALUES ('15', '大兴区', '2');
INSERT INTO `areas` VALUES ('16', '房山区', '2');
INSERT INTO `areas` VALUES ('17', '门头沟区', '2');
INSERT INTO `areas` VALUES ('18', '昌平区', '2');
INSERT INTO `areas` VALUES ('19', '平谷区', '2');
INSERT INTO `areas` VALUES ('20', '密云区', '2');
INSERT INTO `areas` VALUES ('21', '怀柔区', '2');
INSERT INTO `areas` VALUES ('22', '延庆区', '2');

 

2、示意图

 

二、递归查询

 

1、上向下查询

1）代码

with recursive cte as
(
select * from areas where id = 1
union all
select c.* from areas c, cte where c.pid = cte.id
)
select * from cte order by pid, id asc

其中cte为生成结果的临时表名称（该名称可以为任意名称）

areas为查询表名称

注意关系：c.pid = cte.id

 

2）结果

 

2、由下向上

1）代码

with recursive cte as
(
select * from areas where id = 12
union all
select c.* from areas c, cte where c.id = cte.pid
)
select * from cte order by pid, id asc

其中cte为生成结果的临时表名称（该名称可以为任意名称）

areas为查询表名称

注意关系：c.id = cte.pid

 

2）结果