MySQL递归查询,实现上下级联查,父子级查询

最近在工作当中,遇见了有ztree关系的查询:

比如:根据父级的部门ID查询所有的子级部门;

根据父级的区划代码查询所属下级的所有区划信息;

根据子级ID查询隶属行政区划信息;

根据组织机构ID查询所有子级组织机构;

 

数据准备

为了测试,我们首先来创建一张拥有上下级关系的表,这里选择创建一张区划信息表

复制代码
DROP TABLE IF EXISTS `tb_area`;
CREATE TABLE `tb_area` (
`area_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`area_name` varchar(255) DEFAULT NULL,
`area_code` varchar(255) DEFAULT NULL,
`pid` int(10) DEFAULT NULL,
PRIMARY KEY (`area_id`)
) ENGINE=InnoDB AUTO_INCREMENT=131 DEFAULT CHARSET=utf8;

 
复制代码

 

准备数据:

这里给了一些测试数据,如果需要可以参考:

抓取国家统计局区划数据

可以测试数据:

INSERT INTO `tb_area` VALUES ('1''中国''100000''0');
INSERT INTO `tb_area` VALUES ('2''广西壮族自治区''450000000000''1');
INSERT INTO `tb_area` VALUES ('3''防城港市''450600000000''2');
INSERT INTO `tb_area` VALUES ('4''港口区''450602000000''3');
INSERT INTO `tb_area` VALUES ('5''防城区''450603000000''3');
INSERT INTO `tb_area` VALUES ('6''上思县''450621000000''3');
INSERT INTO `tb_area` VALUES ('7''东兴市''450681000000''3');
INSERT INTO `tb_area` VALUES ('8''渔州坪街道''450602001000''4');
INSERT INTO `tb_area` VALUES ('9''白沙叶街道''450602002000''4');
INSERT INTO `tb_area` VALUES ('10''沙潭江街道''450602003000''4');
INSERT INTO `tb_area` VALUES ('11''王府街道''450602004000''4');
INSERT INTO `tb_area` VALUES ('12''企沙镇''450602100000''4');
INSERT INTO `tb_area` VALUES ('13''光坡镇''450602101000''4');
INSERT INTO `tb_area` VALUES ('14''水营街道''450603001000''5');
INSERT INTO `tb_area` VALUES ('15''珠河街道''450603002000''5');
INSERT INTO `tb_area` VALUES ('109''文昌街道''450603003000''5');
INSERT INTO `tb_area` VALUES ('110''大菉镇''450603101000''5');
INSERT INTO `tb_area` VALUES ('111''华石镇''450603102000''5');
INSERT INTO `tb_area` VALUES ('112''那梭镇''450603103000''5');
INSERT INTO `tb_area` VALUES ('113''那良镇''450603104000''5');
INSERT INTO `tb_area` VALUES ('114''峒中镇''450603105000''5');
INSERT INTO `tb_area` VALUES ('115''茅岭镇''450603106000''5');
INSERT INTO `tb_area` VALUES ('116''江山镇''450603107000''5');
INSERT INTO `tb_area` VALUES ('117''扶隆镇''450603108000''5');
INSERT INTO `tb_area` VALUES ('118''滩营乡''450603204000''5');
INSERT INTO `tb_area` VALUES ('119''十万山瑶族乡''450603209000''5');
INSERT INTO `tb_area` VALUES ('120''思阳镇''450621100000''6');
INSERT INTO `tb_area` VALUES ('121''在妙镇''450621101000''6');
INSERT INTO `tb_area` VALUES ('122''华兰镇''450621102000''6');
INSERT INTO `tb_area` VALUES ('123''叫安镇''450621103000''6');
INSERT INTO `tb_area` VALUES ('124''南屏瑶族乡''450621203000''6');
INSERT INTO `tb_area` VALUES ('125''平福乡''450621204000''6');
INSERT INTO `tb_area` VALUES ('126''那琴乡''450621206000''6');
INSERT INTO `tb_area` VALUES ('127''公正乡''450621208000''6');
INSERT INTO `tb_area` VALUES ('128''东兴镇''450681100000''7');
INSERT INTO `tb_area` VALUES ('129''江平镇''450681101000''7');
INSERT INTO `tb_area` VALUES ('130''马路镇''450681102000''7');

 

代码编写

首先我们来写自下而上的查询:

也就是根据下级区划代码ID查询隶属行政区

创建一个函数:

复制代码
DROP FUNCTION IF EXISTS getAreaChildBottomUp;
CREATE FUNCTION getAreaChildBottomUp(areaId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);
SET sTemp = CONCAT(sTemp,',',sTempChd);

SELECT pid INTO sTempChd FROM tb_area WHERE area_id = sTempChd;
WHILE sTempChd <> 0 DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT pid INTO sTempChd FROM tb_area WHERE area_id = sTempChd;
END WHILE;
RETURN sTemp;
END;
复制代码

 

 

查询方法:

复制代码
SELECT
*
FROM
tb_area
WHERE
FIND_IN_SET(
area_id,
getAreaChildBottomUp (113)
);
复制代码

 

 

显示结果:

 

也可以直接使用SQL语句:

复制代码
SELECT
area_id,
area_name,
area_code,
pid
FROM
(
SELECT
@r AS _id,
(
SELECT
          @r := pid
FROM
tb_area
WHERE
area_id = _id
) AS parent_id,
      @l := @l + 1 AS lvl
FROM
(SELECT @r := 113, @l := 0) vars,
tb_area h
WHERE
@r <> 0
) T1
JOIN tb_area T2 ON T1._id = T2.area_id
ORDER BY
area_id;
复制代码

 

 

查出效果相同:

 

下来写自上而下的查询:

也就是根据上级区划代码ID查询隶属下级行政区

创建一个函数:

复制代码
DROP FUNCTION IF EXISTS queryChildFromTopToBottom;
CREATE FUNCTION queryChildFromTopToBottom(areaId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);

WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(area_id) INTO sTempChd FROM tb_area WHERE FIND_IN_SET(pid,sTempChd)>0;
END WHILE;
RETURN sTemp;
END;
复制代码

 

 

查询方法:

复制代码
SELECT
*
FROM
tb_area
WHERE
FIND_IN_SET(
area_id,
queryChildFromTopToBottom (4)
);
复制代码

 

 

查询结果:

欢迎关注摘星族,我们首发于微信公众号:

posted @   路大师_XA  阅读(15147)  评论(1编辑  收藏  举报
点击右上角即可分享
微信分享提示