MySQL-树型结构数据查询

建表

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for treetest-- ----------------------------DROP TABLE IF EXISTS `treetest`;CREATE TABLE `treetest`  (  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id',  `pid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '父id',  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '名字') ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of treetest-- ----------------------------INSERT INTO `treetest` VALUES ('t1', NULL, '根节点');INSERT INTO `treetest` VALUES ('t2', 't1', '一级节点A');INSERT INTO `treetest` VALUES ('t3', 't1', '一级节点B');INSERT INTO `treetest` VALUES ('t4', 't2', '二级节点C');INSERT INTO `treetest` VALUES ('t5', 't2', '二级节点D');INSERT INTO `treetest` VALUES ('t6', 't3', '二级节点E');INSERT INTO `treetest` VALUES ('t7', 't3', '二级节点F');INSERT INTO `treetest` VALUES ('t8', 't4', '三级节点G');INSERT INTO `treetest` VALUES ('t9', 't8', '四级节点H');INSERT INTO `treetest` VALUES ('tA', NULL, '根节点');INSERT INTO `treetest` VALUES ('tB', 'tA', '一级节点1');SET FOREIGN_KEY_CHECKS = 1;

自顶向下

WITH RECURSIVE temp as (SELECT t.* FROM treetest t WHERE t.id = 'tA'UNION ALLSELECT t.* FROM treetest t INNER JOIN temp ON t.pid = temp.id)SELECT * FROM temp

自底向上

• 方法一
  

WITH RECURSIVE temp as (SELECT t.* FROM treetest t WHERE t.id = 't9'UNION ALLSELECT t.* FROM treetest t INNER JOIN temp ON t.id = temp.pid)SELECT * FROM temp

• 方法二
  

SELECT* FROM(SELECT@r AS oid,( SELECT @r := pid FROM treetest WHERE id = oid ) AS pid,@l := @l + 1 AS sort FROM( SELECT @r := 't9', @l := 0 ) vars,treetest h ) t WHEREt.oid IS NOT NULL ORDER BYt.sort ASC

这里加()t这一层是因为最后的@r为Null，条件筛选都是此值，会导致筛选失效

来源地址：https://blog.csdn.net/hw2544622045/article/details/126875693