一、数据库数据
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)结果