【DataBase】MySQL根据父节点查询下面的所有子节点
表结构如下：
/* Navicat Premium Data Transfer Source Server : 主机 Source Server Type : MySQL Source Server Version : 80023 Source Host : localhost:3308 Source Schema : my-info Target Server Type : MySQL Target Server Version : 80023 File Encoding : 65001 Date: 20/06/2021 10:09:06 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for tt_wechat_org -- ---------------------------- DROP TABLE IF EXISTS `tt_wechat_org`; CREATE TABLE `tt_wechat_org` ( `ID` int NOT NULL COMMENT '微信主键', `NAME` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '部门名称', `NAME_EN` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '部门英文名称', `PARENT_ID` int NOT NULL COMMENT '父部门ID', `OEDER` int NOT NULL COMMENT '在父部门中的次序值', `CREATED_BY` bigint NULL DEFAULT NULL, `CREATED_AT` datetime NULL DEFAULT NULL, `UPDATED_BY` bigint NULL DEFAULT NULL, `UPDATED_AT` datetime NULL DEFAULT NULL, PRIMARY KEY (`ID`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '微信组织部门表' ROW_FORMAT = Dynamic;
部门，或者称为组织结构，也是一个树状的层级结构：
业务需求需要从某一个部门开始获取下面的所有子部门
这样来查询每个部门下面的人员，用来查询人员的一些字段
先查询根节点开始，一般根节点的parent_id设置为0，即表示没有上一级的节点了
SELECT * FROM tt_wechat_org WHERE parent_id = 0
输出
+-----------+----------------------------------------------+---------+-----------+-----------+------------+---------------------+------------+---------------------+ | ID | NAME | NAME_EN | PARENT_ID | OEDER | CREATED_BY | CREATED_AT | UPDATED_BY | UPDATED_AT | +-----------+----------------------------------------------+---------+-----------+-----------+------------+---------------------+------------+---------------------+ | 1 | 江铃汽车股份有限公司乘用车销售服务上海分公司 | | 0 | 100000000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 999999999 | 总部 | | 0 | 1 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | +-----------+----------------------------------------------+---------+-----------+-----------+------------+---------------------+------------+---------------------+ 2 rows in set (0.03 sec)
再向下查询又会有更多的子节点：
SELECT * FROM tt_wechat_org WHERE parent_id = 1;
输出：
+-----+-----------------------------+---------+-----------+-----------+------------+---------------------+------------+---------------------+ | ID | NAME | NAME_EN | PARENT_ID | OEDER | CREATED_BY | CREATED_AT | UPDATED_BY | UPDATED_AT | +-----+-----------------------------+---------+-----------+-----------+------------+---------------------+------------+---------------------+ | 2 | 销售及渠道 | | 1 | 100002000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 3 | 数字化 | | 1 | 99999000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 4 | 售后 | | 1 | 99998000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 5 | 市场 | | 1 | 99999500 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 6 | 产品市场 | | 1 | 99996000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 7 | 采购&财务 | | 1 | 99992125 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 45 | 客服支持(DMS/企业微信/商城) | | 1 | 99992250 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 46 | 总经办 | | 1 | 100003000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 49 | 经销商 | | 1 | 99994125 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 240 | 共享素材管理组 | | 1 | 99991125 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 282 | 信息部 | | 1 | 99990125 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 283 | 区域营销广代商 | | 1 | 99989125 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 289 | 临时 | | 1 | 99989625 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 528 | BCG | | 1 | 99988125 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | +-----+-----------------------------+---------+-----------+-----------+------------+---------------------+------------+---------------------+ 14 rows in set (0.06 sec)
如此往复，直到最后没有节点可以查询出来了
1、一个SQL只能查询一次
2、每次查询只能查询出下一级节点的ID
3、下一级节点的ID是下个下一级的Parent_ID
所以需要一个可以递归的SQL
SELECT id, NAME, parent_id FROM ( SELECT * FROM products ORDER BY parent_id, id ) products_sorted, ( SELECT @pv := '19' ) initialisation WHERE find_in_set( parent_id, @pv ) AND length( @pv := concat( @pv, ',', id ))
如果需求要求一些职员是越级的，例如他既是总监，又是经理，管东区又管西区的这种
就需要给他查询多个部门的ID
@pv := '19, 11'
这样也能支持
不过我实现需求的时候还是没采用这种方式，我是用IN关键字做子查询嵌套
一般来说层级关系是固定不变的
例如这一段，给的是大区，然后查询下面所有的分店：
SELECT * FROM tt_wechat_org WHERE parent_id IN( SELECT id FROM tt_wechat_org WHERE parent_id IN( SELECT id FROM tt_wechat_org WHERE parent_id IN(8, 10) ) )
输出：
+-----+----------------------+---------+-----------+-----------+------------+---------------------+------------+---------------------+ | ID | NAME | NAME_EN | PARENT_ID | OEDER | CREATED_BY | CREATED_AT | UPDATED_BY | UPDATED_AT | +-----+----------------------+---------+-----------+-----------+------------+---------------------+------------+---------------------+ | 51 | 成都万星 | | 95 | 100006062 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 55 | 新疆天汇福达 | | 145 | 100002000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 57 | 曲靖明福 | | 157 | 100005312 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 59 | 克拉玛依天宇兴合 | | 145 | 100001750 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 62 | 达州骏骥 | | 95 | 100006000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 63 | 新疆龙泽源 | | 145 | 100001875 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 64 | 德阳万星 | | 95 | 100006125 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 65 | 保山金运 | | 157 | 100005000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 73 | 上海科达 | | 225 | 100000000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 74 | 喀什秋林 | | 145 | 100002375 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 77 | 四川福顺 | | 95 | 100006031 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 80 | 云南明福 | | 157 | 100005500 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 81 | 江西江铃 | | 115 | 100001000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 84 | 昆明健中冈 | | 157 | 100006000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 85 | 龙山万福 | | 157 | 100005125 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 86 | 南充骏耀 | | 95 | 100006375 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 88 | 绵阳万鸿 | | 95 | 100006500 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 96 | 遂宁新清巍 | | 95 | 100002250 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 102 | 浙江江铃嘉兴分公司 | | 101 | 100000000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 110 | 莆田江福 | | 109 | 100000000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 111 | 浙江江铃绍兴分公司 | | 101 | 99999000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 113 | 重庆安博两江分公司 | | 112 | 100000000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 116 | 江西福铃 | | 115 | 100000000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 122 | 湖州万永 | | 101 | 100000500 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 123 | 福州丰骏 | | 109 | 99999093 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 128 | 贵州万佳 | | 127 | 100000000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 129 | 重庆怡之铃 | | 112 | 100000500 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 132 | 兰州赛福 | | 131 | 100000000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 133 | 慈溪友铃 | | 101 | 99999500 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 134 | 贵州万福 | | 127 | 99999000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 146 | 新疆丰骏福瑞 | | 145 | 100002312 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 151 | 宁夏福立升 | | 150 | 100000000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 158 | 文山惠福昇 | | 157 | 100003500 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 160 | 九江江福 | | 115 | 99999000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 163 | 龙岩丰骏福瑞 | | 109 | 99999031 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 168 | 四川中润通汇 | | 95 | 100004000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 174 | 阿克苏秋林 | | 145 | 100003000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 180 | 宁波顺福 | | 101 | 99998125 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 185 | 伊犁尊福 | | 145 | 100001812 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 188 | 泉州国骏 | | 109 | 99999500 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 192 | 嘉峪关良志 | | 131 | 99999000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 194 | 青海嘉悦 | | 193 | 100000000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 196 | 南平华骏 | | 109 | 99999007 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 198 | 漳州华骏天瑞 | | 109 | 99999062 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 199 | 甘肃顺铃 | | 131 | 99998000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 202 | 浙江万捷 | | 101 | 100001000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 203 | 丽水福鑫 | | 101 | 99999250 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 204 | 重庆安福新牌坊分公司 | | 112 | 100001000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 206 | 厦门丰骏福瑞 | | 109 | 99999250 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 209 | 丽江金鸿铭 | | 157 | 100004000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 212 | 上饶星顺 | | 115 | 99998000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 217 | 眉山清巍 | | 95 | 100002500 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 218 | 义乌瑞鑫 | | 101 | 99998250 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 239 | 巴州龙跃 | | 145 | 100002500 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 243 | 温州金跃 | | 101 | 99998500 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 472 | 宁德丰汇 | | 109 | 99999125 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 474 | 三明福元 | | 109 | 99999015 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 488 | 广安骏图 | | 95 | 100007000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 515 | 叶城华联 | | 145 | 99993000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 527 | 贵州林荣 | | 127 | 99998000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 531 | 赣州铃卡 | | 115 | 99996000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 539 | 西藏睿欧 | | 227 | 99999000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 540 | 四川江铃西昌分公司 | | 95 | 100003000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 541 | 宜宾盛晖 | | 95 | 100002125 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 544 | 浙江江铃临安 | | 101 | 99987000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 545 | 台州铭致 | | 101 | 99986000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | | 550 | 台州福奥 | | 101 | 99985000 | NULL | 2021-06-08 14:47:40 | NULL | 2021-06-08 14:47:40 | +-----+----------------------+---------+-----------+-----------+------------+---------------------+------------+---------------------+ 67 rows in set (0.25 sec)
用递归SQL的结果似乎也是一样的：
SELECT * FROM ( SELECT * FROM tt_wechat_org ORDER BY parent_id, id ) products_sorted, ( SELECT @pv := '8,10' ) initialisation WHERE find_in_set( parent_id, @pv ) AND length( @pv := concat( @pv, ',', id )) ORDER BY id
2021年6月26日 21点57分 更新部分：
上一个SQL有一定的问题，例如查询特定层级的ID会失效
后来是改成这个样子实现的：
SELECT TT.id, TT.NAME FROM ( SELECT DISTINCT c2.id, c2.NAME, c2.NAME_EN, c2.parent_id, c1.level-1 LEVEL FROM ( SELECT @ids AS _ids, ( SELECT @ids := GROUP_CONCAT(id) FROM tt_wechat_org WHERE FIND_IN_SET(parent_id, @ids)) AS cids, @l := @l+1 AS LEVEL FROM tt_wechat_org, (SELECT @ids :='49', @l := 0 ) b WHERE @ids IS NOT NULL ) c1, tt_wechat_org c2 WHERE FIND_IN_SET(c2.id, c1._ids) ORDER BY LEVEL , id ) TT
过了一周准备上线，结果生产库用的MySQL8
给爷整无语了，现在叫我们去改业务，8的话容易了，有专用的WITH CURSIVE去写
使用MySQL8的WITH RECURSIVE
WITH RECURSIVE cte AS( SELECT a.id, a.parent_id,a.name FROM tt_wechat_org a WHERE a.id='49' UNION ALL SELECT k.id, k.parent_id,k.name FROM tt_wechat_org k INNER JOIN cte c ON c.id = k.parent_id ) SELECT id,NAME,parent_id FROM cte
改了之后，能跑起来就算赢
测试了之后发现，速度比5.7的快，不知道是网络原因还是8版本的性能好
