表结构如下:
/*
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