With recursive 语句实现 MySQL 递归查询，树形查询

目录

一、问题

二、举例解决

2.1 数据树

 2.2 用SQL创造下这个虚拟树

2.3 实现数据查询

2.4 已有实体表 

查询PID=1的所有子节点：

 查询PID=2的所有子节点：

三、本文参考来源（之一）

---

MYSQL数据库中，时常会用到一种父子结构的表，用于保存分组、分类等，为了表现它们之间的层级关系，分组分类有是又会有多级，使用PID（父ID）和ID（子ID）对的方式是众多方式中的一种。

一、问题

已经建立一个ID，PID关联关系的表结构，想实现查询任意节点下的所有子节点SQL

二、举例解决

2.1 数据树

假设有棵目录结构树：

 

 2.2 用SQL创造下这个虚拟树

    SELECT NULL AS PID, 1 AS ID
        UNION SELECT 1, 11
        UNION ALL SELECT 1, 12
        UNION ALL SELECT 1, 13
        UNION ALL SELECT 12, 121
        UNION ALL SELECT 12, 122
        UNION ALL SELECT 12, 123
        UNION ALL SELECT 122, 1221
        UNION ALL SELECT 122, 1222
        UNION ALL SELECT NULL, 2
        UNION ALL SELECT 2, 21
        UNION ALL SELECT 2, 22
        UNION ALL SELECT 2, 23
        UNION ALL SELECT 23, 231
        UNION ALL SELECT 231, 2311
        UNION ALL SELECT 231, 2312

查询结果：

 

2.3 实现数据查询

现在想查询1的所有子孙，使用with + recursive，来实现，以下代码直接执行，不生成过程表等后遗症：

WITH RECURSIVE 
TB_TREE AS ( -- 这个是虚拟树表，两个字段PID，ID
    SELECT NULL AS PID, 1 AS ID
        UNION SELECT 1, 11
        UNION ALL SELECT 1, 12
        UNION ALL SELECT 1, 13
        UNION ALL SELECT 12, 121
        UNION ALL SELECT 12, 122
        UNION ALL SELECT 12, 123
        UNION ALL SELECT 122, 1221
        UNION ALL SELECT 122, 1222
        UNION ALL SELECT NULL, 2
        UNION ALL SELECT 2, 21
        UNION ALL SELECT 2, 22
        UNION ALL SELECT 2, 23
        UNION ALL SELECT 23, 231
        UNION ALL SELECT 231, 2311
        UNION ALL SELECT 231, 2312
),

TB_TMP AS ( -- 这个递归查询，PID=1的所有子孙
    SELECT PID, ID FROM TB_TREE WHERE PID = '1' -- IS NULL
    UNION
    SELECT T.PID, T.ID FROM TB_TREE T JOIN TB_TMP ON TB_TMP.ID = T.PID 
)
SELECT * FROM TB_TMP;

结果

 

2.4 已有实体表 

如果实体表里已经有实体表了，可以直接使用实体表，不用使用第一个构造虚拟表TB_TREE

查询PID=1的所有子节点：

-- TB_TREE 是实体表，且存在两个字段PID，ID
-- PID：父ID
-- ID：子ID

WITH RECURSIVE 
TB_TMP AS ( -- 这个递归查询，PID=1的所有子孙
    SELECT PID, ID FROM TB_TREE WHERE PID = '1' -- IS NULL
    UNION
    SELECT T.PID, T.ID FROM TB_TREE T JOIN TB_TMP ON TB_TMP.ID = T.PID 
)
SELECT * FROM TB_TMP;

 查询PID=2的所有子节点：

-- TB_TREE 是实体表，且存在两个字段PID，ID
-- PID：父ID
-- ID：子ID

WITH RECURSIVE 
TB_TMP AS ( -- 这个递归查询，PID=1的所有子孙
    SELECT PID, ID FROM TB_TREE WHERE PID = '2' -- IS NULL
    UNION
    SELECT T.PID, T.ID FROM TB_TREE T JOIN TB_TMP ON TB_TMP.ID = T.PID 
)
SELECT * FROM TB_TMP;

通过上面的例子，相信有需要的同学们已经学会怎么使用了。

有问题、意见、建议 +wx13316098767

三、本文参考来源

mysql 递归函数with recursive的用法_cyan_orange的博客-CSDN博客

MySQL-WITH AS用法 - 知乎