案例:
传入一个部门ID,要求查出该部门下所有子部门的ID,包含该传入部门的ID
输入:2(研发部部门ID)
输出:2,5,6,7,8
数据准备:
1、部门表,部门数据
CREATE TABLE `dept` (
`id` bigint NOT NULL,
`parent_id` bigint DEFAULT NULL,
`name` varchar(30) COLLATE utf8mb4_da_0900_as_cs DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_da_0900_as_cs;
INSERT INTO `dept` VALUES (1, 0, '北京总公司');
INSERT INTO `dept` VALUES (2, 1, '北京研发部');
INSERT INTO `dept` VALUES (3, 1, '北京市场部');
INSERT INTO `dept` VALUES (4, 1, '北京运营部');
INSERT INTO `dept` VALUES (5, 2, '研发项目组');
INSERT INTO `dept` VALUES (6, 5, '前端组');
INSERT INTO `dept` VALUES (7, 5, '后端组');
INSERT INTO `dept` VALUES (8, 5, '测试组');
INSERT INTO `dept` VALUES (9, 3, '销售组');
INSERT INTO `dept` VALUES (10, 4, '运营组');
2、部门树结构
SQL语句编写:
mysql8 支持的函数 mysql5 不一定支持,因此两者实现的sql语句也不同
1、mysql8版本
输入:
WITH recursive sub_dept AS (
SELECT
id
FROM
dept
WHERE
dept.id = 2 UNION ALL
SELECT
d.id
FROM
dept d
INNER JOIN sub_dept sub ON d.parent_id = sub.id
) SELECT
id
FROM
sub_dept;
输出:
+------+
| id |
+------+
| 2 |
| 5 |
| 6 |
| 7 |
| 8 |
+------+
5 rows in set (0.00 sec)
输出详情信息:结果满足
+------+-----------+------------+
| id | parent_id | name |
+------+-----------+------------+
| 2 | 1 | 北京研发部 |
| 5 | 2 | 研发项目组 |
| 6 | 5 | 前端组 |
| 7 | 5 | 后端组 |
| 8 | 5 | 测试组 |
+------+-----------+------------+
5 rows in set (0.01 sec)
2、mysql5版本
输入:
SELECT
sub_dept.id
FROM
( SELECT * FROM dept WHERE dept.parent_id IS NOT NULL ) sub_dept,
( SELECT @parent_id := 2 ) pd
WHERE
FIND_IN_SET( parent_id, @parent_id ) > 0
AND @parent_id := concat( @parent_id, ',', id ) UNION
SELECT
dept.id
FROM
dept
WHERE
id = 2;
输出:
+----+
| id |
+----+
| 5 |
| 6 |
| 7 |
| 8 |
| 2 |
+----+
5 rows in set, 6 warnings (0.01 sec)
!!!!!!上述的两种写法,mysql8都支持,但是mysql5不支持第一种写法 !!!!!!