俄语网站叫什么yandex,手机网站宽度多少合适,个人推广网站,网站建设 管理背景#xff1a;
项目升级#xff0c;引入MySQL数据库#xff0c;之前一直用的是Oracle数据#xff0c;在做用户登录单位维护的时候#xff0c;需要返回该用户所属单位下的所有子单位。下边是模拟项目数据实践的过程。
数据准备#xff1a;
准备一张单位表#xff0c…背景
项目升级引入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 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单位| |
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 1501UNION 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|
