mysql 8.0中 树形数据的查询
2023-06-13 09:17:02 时间
文章目录
WITH recursive 表名 AS (
初始语句(非递归部分)
UNION ALL
递归部分语句
)
[ SELECT| INSERT | UPDATE | DELETE]
数据准备
-- ----------------------------
-- Table structure for tree
-- ----------------------------
DROP TABLE IF EXISTS `tree`;
CREATE TABLE `tree` (
`id` int NOT NULL,
`p_id` int DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tree
-- ----------------------------
BEGIN;
INSERT INTO `tree` VALUES (1, NULL, 'A');
INSERT INTO `tree` VALUES (2, NULL, 'B');
INSERT INTO `tree` VALUES (3, 1, 'A3');
INSERT INTO `tree` VALUES (4, 1, 'A4');
INSERT INTO `tree` VALUES (5, 2, 'B5');
INSERT INTO `tree` VALUES (6, 2, 'B6');
INSERT INTO `tree` VALUES (7, 2, 'B7');
INSERT INTO `tree` VALUES (8, 3, 'A3-8');
INSERT INTO `tree` VALUES (9, 3, 'A3-9');
INSERT INTO `tree` VALUES (10, 3, 'A3-10');
INSERT INTO `tree` VALUES (11, 4, 'A4-11');
INSERT INTO `tree` VALUES (12, 4, 'A4-12');
INSERT INTO `tree` VALUES (13, 6, 'B6-13');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
自顶向下查询子树
with RECURSIVE
full_tree (id, p_id, name) AS
(select id, p_id, name from tree where p_id is null -- 查询条件
union all
select t.id, t.p_id, t.name from tree t
inner join full_tree on full_tree.id = t.p_id)
select * from full_tree;
查询结果:
自底向上查找所有节点
with RECURSIVE
filter_tree (id, p_id, name) AS
(select id, p_id, name from tree where name like 'B%' -- 过滤条件
union all
select t.id, t.p_id, t.name from tree t
inner join filter_tree on filter_tree.p_id = t.id)
select distinct * from filter_tree;
查询结果:
根据子节点id向上查找
with RECURSIVE
filter_tree (id, p_id, name) AS
(select id, p_id, name from tree where id=13 -- 过滤条件
union all
select t.id, t.p_id, t.name from tree t
inner join filter_tree on filter_tree.p_id = t.id)
select distinct * from filter_tree;
查询结果:
到此,本章内容就介绍完啦
相关文章
- MySQL测试:深入了解你的数据集(mysql测试数据集)
- MySQL精准解析:智能提取数据价值(mysql解析数据)
- MySQL中查询数据,生成精美表格(mysql查询生成表)
- 优化MySQL优化之清理无用数据(mysql清理)
- 掌握MySQL触发器:深入挖掘查询究竟如何实现(mysql触发器查询)
- MySQL连续执行命令的技巧(mysql连续执行命令)
- 循环MySQL中使用while循环编写查询语句(mysql的while)
- MySQL联表查询:精彩运用让数据变得更强大(mysql联表查询)
- 利用MySQL实现数据分割——逗号分隔技巧(mysql用逗号分隔)
- 集群MHA: Building a MySQL Cluster(mha搭建mysql)
- MySQL 数据管理大师 — 实现高效的数据操作(mysql数据工具)
- 数据超高效:MySQL为千万数据保驾护航(数据库mysql千万)
- MySQL 中实现模糊查询的优化策略(mysql模糊查询优化)
- MySQL中设置多个字段主键的研究(mysql多字段主键)
- 如何利用MySQL慢查询日志提高数据库性能(mysql个慢查询日志)
- MYSQL如何搜索包含特定字段的数据(mysql 中包含字段)
- MySQL实现一对多数据关系(mysql一对多实现)
- 深入剖析MySQL一主多从架构,打造高可用的数据库系统(mysql一主多从架构)
- 解决MySQL配置向导缺失问题(mysql不出现配置向导)
- MySQL两字段的值详解如何在MySQL中查询并比较两个字段的值(mysql两字段的值)