SQL 双亲节点查找所有子节点

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 怎么保存树状结构的数据呢?在 SQL 中常用的是双亲节点法。创建表如下 CREATE TABLE category ( id LONG, parentId LONG, name String(20) ) INSERT INTO category VALUES ( 1, NULL, 'Roo...

怎么保存树状结构的数据呢?在 SQL 中常用的是双亲节点法。创建表如下

CREATE TABLE category ( id LONG, parentId LONG, name String(20) )
 
INSERT INTO category VALUES ( 1, NULL, 'Root' )
INSERT INTO category VALUES ( 2, 1, 'Branch1' )
INSERT INTO category VALUES ( 3, 1, 'Branch2' )
INSERT INTO category VALUES ( 4, 3, 'SubBranch1' )
INSERT INTO category VALUES ( 5, 2, 'SubBranch2' )

其中,parent id 表示父节点, name 是节点名称。

假设当前欲获取某一节点下所有子节点(获取后代 Descendants),该怎么做呢?如果使用程序(Java/PHP)递归调用,那么将在数据库与本地开发语言之间来回访问,效率之低可想而知。于是我们希望在数据库的层面就可以完成,——该怎么做呢?

递归法

经查询,最好的方法(个人觉得)是 SQL 递归 CTE 的方法。所谓 CTE 是 Common Table Expressison 公用表表达式的意思。网友评价说:“CTE 是一种十分优雅的存在。CTE 所带来最大的好处是代码可读性的提升,这是良好代码的必须品质之一。使用递归 CTE 可以更加轻松愉快的用优雅简洁的方式实现复杂的查询。”——其实我对 CTE 了解有效,大家谷歌下其意思即可。

怎么用 CTE 呢?我们用小巧数据库 SQLite,它就支持!别看他体积不大,却也能支持最新 SQL99 的 with 语句,例子如下。

WITH w1( id, parentId, name) AS 
(		SELECT 
			category.id, 
			category.parentId,  
                        category.name
		FROM 
			category 
		WHERE 
			id = 1
	UNION ALL 
		SELECT 
			category.id, 
			category.parentId,  
                        category.name
		FROM 
			category JOIN w1 ON category.parentId= w1.id
) 
SELECT * FROM w1;

其中 WHERE id = 1 是那个父节点之 id,你可以改为你的变量。简单说,递归 CTE 最少包含两个查询(也被称为成员)。第一个查询为定点成员,定点成员只是一个返回有效表的查询,用于递归的基础或定位点。第二个查询被称为递归成员,使该查询称为递归成员的是对 CTE 名称的递归引用是触发。在逻辑上可以将 CTE 名称的内部应用理解为前一个查询的结果集。递归查询没有显式的递归终止条件,只有当第二个递归查询返回空结果集或是超出了递归次数的最大限制时才停止递归。递归次数上限的方法是使用 MAXRECURION。

相应地给出查找所有父节点的方法(获取祖先 Ancestors,就是把 id 和 parentId 反过来)

WITH w1( id, parentId, name, level) AS   
(       SELECT   
                id,   
                parentId,    
                name,
                0 AS level
            FROM   
                category   
            WHERE   
                id = 6  
        UNION ALL   
            SELECT   
                category.id,   
                category.parentId,    
                category.name ,
                level + 1
            FROM   
                category JOIN w1 ON category.id= w1.parentId
 )   
SELECT * FROM w1; 

无奈的 MySQL

SQLite ok 了,而 MySQL 呢?

在另一边厢,大家都爱用的 MySQL 却无视 with 语句,官网博客上明确说明是压根不支持,十分不方便,明明可以很简单事情为什么不能用呢?——而且 MySQL 也好像没有计划在将来的新版本中添加 with 的 cte 功能。于是大家想出了很多办法。其实不就是一个递归程序么——应该不难——写函数或者存储过程总该行吧?没错,的确如此,——写递归不是问题,问题是用 SQL 写就是个问题——还是那句话,“隔行如隔山”,虽然有点夸张的说法,但我想既懂数据库又懂各种数据库方言写法(存储过程)的人应该不是很多吧~,——不细究了,反正就是代码帖来贴去呗~

我这里就不贴 SQL 了,可以看这里的,《MySQL中进行树状所有子节点的查询》 http://blog.csdn.net/acmain_chm/article/details/4142971

至此,我们的目的可以说已经达到了,而且还不错,因为这是不限层数的(以前 CMS 常说的“无限级”分类)。——其实,一般情况下,层数超过三层就很多,很复杂了,一般用户如无特殊需求,也用不上这么多层。于是,在给定层数的约束下,可以写标准的 SQL 来完成该任务——尽管有点写死的感觉~~

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parentId = t1.id
LEFT JOIN category AS t3 ON t3.parentId = t2.id
LEFT JOIN category AS t4 ON t4.parentId = t3.id
WHERE t1.id= 1

相应地给出查找所有父节点的方法(获取祖先 Ancestors,就是把 id 和 parentId 反过来)

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4  
FROM category AS t1  
	LEFT JOIN category AS t2 ON t2.id= t1.parentId 
	LEFT JOIN category AS t3 ON t3.id= t2.parentId 
	LEFT JOIN category AS t4 ON t4.id= t3.parentId 
WHERE t1.id= 10

优化版本

但是生成的结果和第一个例子相比起来有点奇怪,而且不好给 Java 用,——那就再找找其他例子

SELECT    
            p1.id,
            p1.name,
            p1.parentId as parentId,
            p2.parentId as parent2_id,
            p3.parentId as parent3_id,
            p4.parentId as parent4_id,
            p5.parentId as parent5_id,
			p6.parentId as parent6_id
FROM	category p1
LEFT JOIN   category p2 on p2.id = p1.parentId 
LEFT JOIN   category p3 on p3.id = p2.parentId 
LEFT JOIN   category p4 on p4.id = p3.parentId  
LEFT JOIN   category p5 on p5.id = p4.parentId  
LEFT JOIN   category p6 on p6.id = p5.parentId
WHERE 1 IN   (p1.parentId, 
                   p2.parentId, 
                   p3.parentId, 
                   p4.parentId, 
                   p5.parentId, 
                   p6.parentId) 
ORDER BY 1, 2, 3, 4, 5, 6, 7;

这个总算像点样子了,结果是这样子的。

相应地给出查找所有父节点的方法(获取祖先 Ancestors,就是把 id 和 parentId 反过来, 还有改改 IN 里面的字段名)

SELECT      
                p1.id,  
                p1.name,  
                p1.parentId as parentId,  
                p2.parentId as parent2_id,  
                p3.parentId as parent3_id
    FROM    category p1  
    LEFT JOIN   category p2 on p2.parentId   = p1.id
    LEFT JOIN   category p3 on p3.parentId   = p2.id
    WHERE 9 IN   (p1.id,   
                       p2.id,   
                       p3.id)   
    ORDER BY 1, 2, 3;  

这样就很通用啦~无论你 SQLite 还是 MySQL。

其他查询:

查询直接子节点的总数:

SELECT c.*
,       (SELECT COUNT(*)  FROM category c2 WHERE c2.parentId = c.id) 
        AS direct_children
FROM category c
  • 使用 with 语句递归,通俗易懂的例子(英文),我第一个成功的例子就是从这里 copy 的,另外还可以查层数 level 和反向的父节点:https://www.valentina-db.com/dokuwiki/doku.php?id=valentina:articles:recursive_query
  • 标准写法的出处(英文):http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query
  • 很好的总结贴(英文):http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
  • SQlite with 语句用法中文翻译(太晦涩,不懂鸟) http://blog.csdn.net/aflyeaglenku/article/details/50978986
  • 利用闭包做的树结构(书上说这个方法最好,但同时觉得也很高级,英文)http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
14天前
leetcode-SQL-608. 树节点
leetcode-SQL-608. 树节点
19 0
|
14天前
|
SQL 数据挖掘 数据处理
「SQL面试题库」 No_36 树节点
「SQL面试题库」 No_36 树节点
|
SQL 前端开发 关系型数据库
探索MySQL-Cluster奥秘系列之SQL节点故障测试(10)
在这一小节中,我继续来对MySQL-Cluster集群环境的高可用性进行测试,接下来我们来看下当SQL节点出现故障时,MySQL-Cluster集群环境是如何保障其高可用性的。
226 0
|
SQL 关系型数据库 MySQL
探索MySQL-Cluster奥秘系列之SQL节点和数据节点配置(7)
上一小节中,我们讲解了MySQL-Cluster集群的管理节点的配置方法,在这一小节中,我们来学习下关于SQL节点和数据节点的配置方法。
429 0
|
SQL 存储 关系型数据库
探索MySQL-Cluster奥秘系列之SQL节点(3)
在上一小节中,我们对MySQL-Cluster集群的管理节点进行了介绍,那么接下来,我们继续进行讲解,来了解下MySQL-Cluster集群的SQL节点的作用和功能。
128 0
|
SQL 消息中间件 JavaScript
SQL查找是否"存在",别再count了!
SQL查找是否"存在",别再count了!
|
SQL 存储
在 SQL Server 中查找活动连接和死锁
在SQL Server中有几种方法可以找到活动的 SQL 连接。让我们看看一些使用 T-SQL 查询的简单快捷的方法。
179 0
|
SQL Java 程序员
SQL 查找是否“存在“,别再 count 了!
根据某一条件从数据库表中查询 『有』 与 『没有』 ,只有两种状态, 那为什么在写SQL的时候,还要SELECT count(*) 呢?无论是刚入道的程序员新星,还是精湛沙场多年的程序员老白,都是一如既往的count
112 0
SQL 查找是否“存在“,别再 count 了!
|
存储 SQL 关系型数据库
Polardb-X 多存储节点下sql执行计划
在单机上创建polardb-X两存储节点集群,查看执行计划
209 0
|
SQL 程序员 数据库
SQL 查找是否"存在",别再 COUNT 了,很耗费时间的
根据某一条件从数据库表中查询 『有』与『没有』,只有两种状态,那为什么在写SQL的时候,还要SELECT COUNT(*) 呢? 无论是刚入道的程序员新星,还是精湛沙场多年的程序员老白,都是一如既往的COUNT