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

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

比如：根据父级的部门ID查询所有的子级部门；

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

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

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

数据准备

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

<span style="color: #000000;">DROP TABLE IF EXISTS `tb_area`;
CREATE TABLE `tb_area` (
`area_id` </span><span style="color: #0000ff;">int</span>(10<span style="color: #000000;">) unsigned NOT NULL AUTO_INCREMENT,
`area_name` varchar(</span>255<span style="color: #000000;">) DEFAULT NULL,
`area_code` varchar(</span>255<span style="color: #000000;">) DEFAULT NULL,
`pid` </span><span style="color: #0000ff;">int</span>(10<span style="color: #000000;">) DEFAULT NULL,
PRIMARY KEY (`area_id`)
) ENGINE</span>=InnoDB AUTO_INCREMENT=131 DEFAULT CHARSET=<span style="color: #000000;">utf8;
 </span>

准备数据：

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

抓取国家统计局区划数据

可以测试数据：

<code class="hljs">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‘);</code>

代码编写

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

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

创建一个函数：

<span style="color: #000000;">DROP FUNCTION IF EXISTS getAreaChildBottomUp;
CREATE FUNCTION getAreaChildBottomUp(areaId INT)
RETURNS VARCHAR(</span>4000<span style="color: #000000;">)
BEGIN
DECLARE sTemp VARCHAR(</span>4000<span style="color: #000000;">);
DECLARE sTempChd VARCHAR(</span>4000<span style="color: #000000;">);
SET sTemp</span>=‘$‘<span style="color: #000000;">;
SET sTempChd </span>=<span style="color: #000000;"> CAST(areaId AS CHAR);
SET sTemp </span>= CONCAT(sTemp,‘,‘<span style="color: #000000;">,sTempChd);
SELECT pid INTO sTempChd FROM tb_area WHERE area_id </span>=<span style="color: #000000;"> sTempChd;
WHILE sTempChd </span><> 0<span style="color: #000000;"> DO
SET sTemp </span>= CONCAT(sTemp,‘,‘<span style="color: #000000;">,sTempChd);
SELECT pid INTO sTempChd FROM tb_area WHERE area_id </span>=<span style="color: #000000;"> sTempChd;
END WHILE;
RETURN sTemp;
END;</span>

查询方法：

<span style="color: #000000;">SELECT
</span>*<span style="color: #000000;">
FROM
tb_area
WHERE
FIND_IN_SET(
area_id,
getAreaChildBottomUp (</span>113<span style="color: #000000;">)
);</span>

显示结果：

?

也可以直接使用SQL语句：

<span style="color: #000000;">SELECT
area_id,
area_name,
area_code,
pid
FROM
(
SELECT
@r AS _id,
(
SELECT
          @r :</span>=<span style="color: #000000;"> pid
FROM
tb_area
WHERE
area_id </span>=<span style="color: #000000;"> _id
) AS parent_id,
      @l :</span>= @l + 1<span style="color: #000000;"> AS lvl
FROM
(SELECT @r :</span>= 113, @l := 0<span style="color: #000000;">) vars,
tb_area h
WHERE
@r </span><> 0<span style="color: #000000;">
) T1
JOIN tb_area T2 ON T1._id </span>=<span style="color: #000000;"> T2.area_id
ORDER BY
area_id;</span>

查出效果相同：

?

下来写自上而下的查询：

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

创建一个函数：

<span style="color: #000000;">DROP FUNCTION IF EXISTS queryChildFromTopToBottom;
CREATE FUNCTION queryChildFromTopToBottom(areaId INT)
RETURNS VARCHAR(</span>4000<span style="color: #000000;">)
BEGIN
DECLARE sTemp VARCHAR(</span>4000<span style="color: #000000;">);
DECLARE sTempChd VARCHAR(</span>4000<span style="color: #000000;">);
SET sTemp</span>=‘$‘<span style="color: #000000;">;
SET sTempChd </span>=<span style="color: #000000;"> CAST(areaId AS CHAR);
WHILE sTempChd IS NOT NULL DO
SET sTemp</span>= CONCAT(sTemp,‘,‘<span style="color: #000000;">,sTempChd);
SELECT GROUP_CONCAT(area_id) INTO sTempChd FROM tb_area WHERE FIND_IN_SET(pid,sTempChd)</span>>0<span style="color: #000000;">;
END WHILE;
RETURN sTemp;
END;</span>

查询方法：

<span style="color: #000000;">SELECT
</span>*<span style="color: #000000;">
FROM
tb_area
WHERE
FIND_IN_SET(
area_id,
queryChildFromTopToBottom (</span>4<span style="color: #000000;">)
);</span>

查询结果：

?

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

?

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

标签：eset   自治区   line   log   tps   resize   container   sel   效果