建表 SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure f
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
--结束END--
本文标题: MySQL-树型结构数据查询
本文链接: https://lsjlt.com/news/392746.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
2024-10-23
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0