项目升级,引入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);
主要使用START WITH
和CONNECT 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单位| |
主要是使用关键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|