递归查询用于查询树形结构的列表,比如行政区列表。包括向下递归查询:根据父级查询子级;向上查询:根据子级查询父级。mysql需要使用存储函数,oracle可以使用connect by语句直接查询。
MySQL递归查询
首先创建一张表
DROP TABLE IF EXISTS `tb_base_region`;
CREATE TABLE `tb_base_region` (
`PKID` int(10) NOT NULL AUTO_INCREMENT,
`REGION_CODE` varchar(16) NOT NULL,
`REGION_NAME` varchar(40) NOT NULL,
`SHORT_NAME` varchar(20) DEFAULT NULL,
`PARENT_CODE` varchar(16) DEFAULT NULL,
`REGION_LEVEL` int(2) DEFAULT NULL,
`STATUS` varchar(2) DEFAULT NULL,
`REMARKS` varchar(10) DEFAULT NULL,
PRIMARY KEY (`PKID`)
) ENGINE=InnoDB AUTO_INCREMENT=385 DEFAULT CHARSET=utf8;
插入数据
INSERT INTO `tb_base_region` VALUES (601, '01', '中华人民共和国', '中国', NULL, 1, '1', NULL);
INSERT INTO `tb_base_region` VALUES (602, '0101', '河北省', '冀', '01', 2, '1', NULL);
INSERT INTO `tb_base_region` VALUES (603, '0102', '河南省', '豫', '01', 2, '1', NULL);
INSERT INTO `tb_base_region` VALUES (604, '010101', '石家庄市', '石家庄', '0101', 3, '1', NULL);
INSERT INTO `tb_base_region` VALUES (605, '010102', '保定市', '保定', '0101', 3, '1', NULL);
INSERT INTO `tb_base_region` VALUES (606, '010201', '郑州市', '郑州', '0102', 3, '1', NULL);
INSERT INTO `tb_base_region` VALUES (607, '010202', '新乡市', '新乡', '0102', 3, '1', NULL);
MySQL递归查询需要用到存储函数,使用存储函数查询出符合要求的数据的region_code集合,以逗号分隔拼接成字符串。然后使用FIND_IN_SET(region_code,存储函数返回结果)函数查询出需要的数据。
MySQL递归查询存储函数如下:
-- 创建向下查询的存储函数,查询出符合要求的数据的region_code集合,以逗号拼接成字符串。
CREATE FUNCTION getRegionCodeListForDown(upCode VARCHAR(10))
RETURNS VARCHAR(4096)
BEGIN
DECLARE returnCodeList VARCHAR(4096);
DECLARE selectCode VARCHAR(4096);
SET returnCodeList = '';
SET selectCode = upCode;
WHILE selectCode IS NOT NULL DO
SET returnCodeList = CONCAT(returnCodeList,',',selectCode);
select GROUP_CONCAT(region_code) INTO selectCode FROM tb_base_region WHERE FIND_IN_SET(parent_code,selectCode) > 0;
END WHILE;
RETURN returnCodeList;
END;
-- 创建向上查询的存储函数,查询出符合要求的数据的region_code集合。
CREATE FUNCTION getRegionCodeListForUp(downCode VARCHAR(10))
RETURNS VARCHAR(4096)
BEGIN
DECLARE returnCodeList VARCHAR(4096);
DECLARE selectCode VARCHAR(4096);
SET returnCodeList = '';
SET selectCode = downCode;
-- WHILE selectCode IS NOT NULL DO -- 判断条件视情况而定
WHILE selectCode >= 0 DO
SET returnCodeList = CONCAT(returnCodeList,',',selectCode);
SELECT parent_code into selectCode from tb_base_region WHERE region_code = selectCode;
END WHILE;
RETURN returnCodeList;
END;
语句中 concat(a,b,c) 函数可以将a、b、c三个字符串做拼接;
group_concat(a) into a 语句可以将 a 字段查询结果以逗号为分隔符做拼接,拼接结果放入 a 字段。
group_concat(a) as a .... group by b 语句可以按 b 字段分组,将 a 字段查询结果以逗号分割符做拼接,拼接结果放入 a 字段。
find_in_set(a,b) > 0 语句可以筛选出符合“ a 字段中的数据存在于 b 参数列举的值中 ”条件的数据。
这样一来就可以使用存储函数实现递归查询
-- 向下查询(根据父级查询子级)
select * from tb_base_region WHERE FIND_IN_SET(region_code,getRegionCodeListForDown('01'));
-- 向上查询(根据子级查询父级)
select * from tb_base_region WHERE FIND_IN_SET(region_code,getRegionCodeListForUp('010101'));
Oracle递归查询
oracle提供了递归查询的语句:start with ... connect by ... prior
start with:递归查询的起始节点
connect by:递归查询的连接条件
prior:prior和放在parent_code前代表向上查询;prior放在code前代表向下查询;
connect by prior region_doce = parent_code:向下查询;
connect by region_code = prior parent_code:向上查询。</