環(huán)境
需要MySQL8.0+
上下級(jí)關(guān)系圖
image
建表語(yǔ)句
DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept` (
`id` int(10) NOT NULL,
`pid` int(10) NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `t_dept` VALUES (0, NULL, '1');
INSERT INTO `t_dept` VALUES (1, 0, '11');
INSERT INTO `t_dept` VALUES (2, 0, '12');
INSERT INTO `t_dept` VALUES (3, 1, '111');
INSERT INTO `t_dept` VALUES (4, 1, '112');
INSERT INTO `t_dept` VALUES (5, 2, '121');
INSERT INTO `t_dept` VALUES (6, 4, '1121');
INSERT INTO `t_dept` VALUES (7, 4, '1122');
INSERT INTO `t_dept` VALUES (8, 4, '1123');
INSERT INTO `t_dept` VALUES (9, 5, '1211');
image
查詢上下級(jí)sql語(yǔ)句
遞歸霎俩，查詢本級(jí)以及所有下級(jí)
# 遞歸抚吠，查詢本級(jí)以及所有下級(jí)
WITH RECURSIVE temp as (
SELECT t.* FROM t_dept t WHERE id = 0
UNION ALL
SELECT t.* FROM t_dept t INNER JOIN temp ON t.pid = temp.id
)
SELECT * FROM temp;
image
遞歸覆劈，查詢所有下級(jí)
# 遞歸欲险，查詢所有下級(jí)
WITH RECURSIVE temp as (
SELECT t.* FROM t_dept t WHERE id = 0
UNION ALL
SELECT t.* FROM t_dept t INNER JOIN temp ON t.pid = temp.id
)
SELECT * FROM temp WHERE id != 0;
image
遞歸台谍，查詢本級(jí)及所有直屬上級(jí)
# 遞歸须喂，查詢本級(jí)及所有直屬上級(jí)
WITH RECURSIVE temp as (
SELECT t.* FROM t_dept t WHERE id = 8
UNION ALL
SELECT t.* FROM t_dept t INNER JOIN temp ON t.id = temp.pid
)
SELECT * FROM temp;
image
遞歸，查詢所有直屬上級(jí)
# 遞歸，查詢所有直屬上級(jí)
WITH RECURSIVE temp as (
SELECT t.* FROM t_dept t WHERE id = 8
UNION ALL
SELECT t.* FROM t_dept t INNER JOIN temp ON t.id = temp.pid
)
SELECT * FROM temp WHERE id != 8;
image.png
