Mysql8和Oracle实际项目中递归查询树形结构

发布时间:2023年12月18日

背景:

项目升级,引入MySQL数据库,之前一直用的是Oracle数据,在做用户登录单位维护的时候,需要返回该用户所属单位下的所有子单位。下边是模拟项目数据实践的过程。

数据准备:

准备一张单位表,里面存储下级单位以及上级单位的对应关系数据。

-- 创建单位表
CREATE TABLE UNITS (
  ID INT PRIMARY KEY,
  NAME VARCHAR(255),
  PARENT_ID INT
);

-- 插入数据
INSERT INTO UNITS (ID, NAME, PARENT_ID)
VALUES
  (1500, '1500单位', NULL),
  (1501, '1501单位', 1500),
  (15011, '15011单位', 1501),
  (15012, '15012单位', 1501),
  (150121, '150121单位', 15012),
  (1600, '1600单位', NULL),
  (1601, '1601单位', 1600),
  (1602, '1602单位', 1600);

实践:

1、Oracle做法

主要使用START WITHCONNECT BY PRIOR完成递归查询,多用于层次查询。
START WITH:表示递归的起始记录。
CONNECT BY PRIOR:表示递归时与当前记录的关联关系,其中PRIOR的位置是是可以变动的,位置的不同,其查询结果也不同。

所有顶级单位向下查询属于该单位的所有的子单位:
# 使用START WITH和CONNECT BY PRIOR完成递归查询,以所有PARENT_ID IS NULL的记录为起始记录,关联表中的ID字段,查询所有属于下级单位
SELECT * FROM UNITS U CONNECT BY PRIOR ID = PARENT_ID START WITH PARENT_ID IS NULL;


ID    |NAME    |PARENT_ID|
------|--------|---------|
  1500|1500单位  |         |
  1501|1501单位  |     1500|
 15011|15011单位 |     1501|
 15012|15012单位 |     1501|
150121|150121单位|    15012|
  1600|1600单位  |         |
  1601|1601单位  |     1600|
  1602|1602单位  |     1600|

根据指定单位向下查询属于该单位的所有的子单位:
# 指定查询1600单位下的所有的子单位,注意向下查找,PRIOR的位置在ID的前边
SELECT * FROM UNITS U CONNECT BY PRIOR ID = PARENT_ID START WITH PARENT_ID = 1600;

ID  |NAME  |PARENT_ID|
----|------|---------|
1601|1601单位|     1600|
1602|1602单位|     1600|

根据子单位向上查询属于该单位的所有的上级单位,如果不要包含指定的子单位,那就在结果集中过滤掉即可:
# 查询1501的所有的上级单位,注意向上查找,PRIOR的位置在PARENT_ID的前边
SELECT * FROM UNITS U CONNECT BY ID = PRIOR PARENT_ID START WITH ID = 1501;

ID  |NAME  |PARENT_ID|
----|------|---------|
1501|1501单位|     1500|
1500|1500单位|         |

2、Mysql做法

主要是使用关键WITH RECURSIVE 进行递归查询,不过要求,Mysql的版本需要在8.0以上。

所有顶级单位向下查询属于该单位的所有的子单位:
# WITH RECURSIVE是MySQL8支持的递归关键字。
WITH RECURSIVE UNITS_PARENT (ID ,NAME, PARENT_ID) AS (
	# 递归体的开始,所有的父级下子单位,根据这个语句产生递归体的初始行,并将这些初始行数据插入到UNITS_PARENT临时表中。
    SELECT U.ID, U.NAME, U.PARENT_ID FROM UNITS AS U WHERE U.PARENT_ID IS NULL 
    UNION ALL 
    # 递归的开始,将根据UNITS_PARENT表中的初始行数据和UNITS表进行真正的递归查询,直到不在产生新的数据行为止,也就是递归结束,并将查询的数据插入到UNITS_PARENT临时表中。
    # 注意ON的条件:UNITS_PARENT中初始行数据存的是所有的上级单位信息,向下查询,那就是查询所有的PARENT_ID等于初始行ID的数据。
    SELECT U.ID, U.NAME, U.PARENT_ID FROM UNITS_PARENT AS UP JOIN UNITS AS U ON UP.ID = U.PARENT_ID
)
# 从UNITS_PARENT临时表查询出所有的符合要求的数据
SELECT ID ,NAME, PARENT_ID FROM UNITS_PARENT ORDER BY ID;


#|ID    |NAME    |PARENT_ID|
-+------+--------+---------+
1|  1500|1500单位  |         |
2|  1501|1501单位  |     1500|
3|  1600|1600单位  |         |
4|  1601|1601单位  |     1600|
5|  1602|1602单位  |     1600|
6| 15011|15011单位 |     1501|
7| 15012|15012单位 |     1501|
8|150121|150121单位|    15012|

根据指定单位向下查询属于该单位的所有的子单位:
# WITH RECURSIVE是MySQL8支持的递归关键字。
WITH RECURSIVE UNITS_PARENT (ID ,NAME, PARENT_ID) AS (
	# 递归体的开始,1600下子单位,根据这个语句产生递归体的初始行,并将这些初始行数据插入到UNITS_PARENT临时表中。
    SELECT U.ID, U.NAME, U.PARENT_ID FROM UNITS AS U WHERE U.PARENT_ID = 1600 
    UNION ALL 
    # 递归的开始,将根据UNITS_PARENT表中的初始行数据和UNITS表进行真正的递归查询,直到不在产生新的数据行为止,也就是递归结束,并将查询的数据插入到UNITS_PARENT临时表中。
    # 注意ON的条件:UNITS_PARENT中初始行数据存的是1600单位信息,向下查询,那就是查询所有的PARENT_ID等于初始行1600 ID的数据。
    SELECT U.ID, U.NAME, U.PARENT_ID FROM UNITS_PARENT AS UP JOIN UNITS AS U ON UP.ID = U.PARENT_ID
)
# 从UNITS_PARENT临时表查询出所有的符合要求的数据
SELECT ID ,NAME, PARENT_ID FROM UNITS_PARENT ORDER BY ID;


#|ID  |NAME  |PARENT_ID|
-+----+------+---------+
1|1601|1601单位|     1600|
2|1602|1602单位|     1600|

根据子单位向上查询属于该单位的所有的上级单位,如果不要包含指定的子单位,那就在结果集中过滤掉即可:
# WITH RECURSIVE是MySQL8支持的递归关键字。
WITH RECURSIVE UNITS_PARENT (ID ,NAME, PARENT_ID) AS (
	# 递归体的开始,查询1501的所有上级单位,根据这个语句产生递归体的初始行,并将这些初始行数据插入到UNITS_PARENT临时表中。
    SELECT U.ID, U.NAME, U.PARENT_ID FROM UNITS AS U WHERE U.ID = 1501
    UNION ALL 
    # 递归的开始,将根据UNITS_PARENT表中的初始行数据和UNITS表进行真正的递归查询,直到不在产生新的数据行为止,也就是递归结束,并将查询的数据插入到UNITS_PARENT临时表中。
    # 注意ON的条件:UNITS_PARENT中初始行数据存的是1501单位信息,向上查询,那就是查询所有ID等于初始行PARENT_ID的数据。
    SELECT U.ID, U.NAME, U.PARENT_ID FROM UNITS_PARENT AS UP JOIN UNITS AS U ON U.ID = UP.PARENT_ID
)
# 从UNITS_PARENT临时表查询出所有的符合要求的数据
SELECT ID ,NAME, PARENT_ID FROM UNITS_PARENT ORDER BY ID;


#|ID  |NAME  |PARENT_ID|
-+----+------+---------+
1|1500|1500单位|         |
2|1501|1501单位|     1500|

文章来源:https://blog.csdn.net/qq_22610595/article/details/134938447
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。