在银行的统计分析任务中,往往是需要查询本行及其下级行、下级行的支行等各机构各自的运营情况,入参可以能是总行,也可能是一级行或二级行甚至支行,如果针对每种情况都各种写一个查询语句,工作量过于繁杂,但用了递归查询,就可以一劳永逸了;
下面介绍一下递归查询的格式:
WITH RECURSIVE recursive_cte_name (column1, column2, ...) AS (
-- 非递归部分(基础情况)
SELECT column1, column2, ...
FROM your_table
WHERE some_conditions
UNION ALL
-- 递归部分
SELECT column1, column2, ...
FROM your_table
JOIN recursive_cte_name ON recursive_join_condition
WHERE recursive_termination_condition (可省略)
)
SELECT * FROM recursive_cte_name;//查询出本次递归查询的结果集
在这个格式中:
- WITH RECURSIVE 关键字用来定义一个递归的公共表表达式(CTE)。
- recursive_cte_name 是你为递归CTE定义的名称。
- column1, column2, ... 是CTE中包含的列(可省略)。
- 非递归部分(SELECT 语句)是递归查询的起点,它返回一组初始结果。
- UNION ALL 关键字用来合并非递归部分和递归部分的结果。
- 递归部分包含一个JOIN操作,它将your_table与递归CTE自身连接起来。
- recursive_join_condition 是连接递归CTE和原始表的条件。
- recursive_termination_condition 是递归终止的条件,它决定了递归何时停止。
一般来说递归终止条件都是隐式的,当本次递归的结果为空时候,递归查询就终止了,mqsql也有自己的递归层级限制,一般是100层,当递归到相应的层级就会强制结束递归,也可以自定义递归条件,在结果集中添加深度字段(depth),第一次非递归查询的depth=1,此后每次递归的depth=上次递归结果集的depth+1,然后再递归结束的条件限制depth的大小;
例如:
WITH RECURSIVE department_tree (department_id, department_name, parent_department_id, depth, path) AS (
SELECT
department_id,
department_name,
parent_department_id,
1 AS depth --将第一次原始查询的depth深度字段设置为1
FROM company_department
WHERE parent_department_id IS NULL
UNION ALL
SELECT
cd.department_id,
cd.department_name,
cd.parent_department_id,
dt.depth + 1 AS depth --此后每次查询的depth=上一次查询的深度+1
FROM company_department cd
JOIN department_tree dt ON cd.parent_department_id = dt.department_id
where depth<5 --当depth=4的时候还会进入递归查询,查出depth=5的数据,查出的结果就无法作为递归的条件进入了,因为条件不成立了,所以就是查询了5层
)
SELECT
department_id, department_name, parent_department_id, depth, path
FROM department_tree
ORDER BY path;
示例:
假设我们有一个名为employees的表,其中包含员工的层级关系,每个员工都有一个上级(除了顶级员工外)。我们想要递归地查询某个员工的所有下属。
WITH RECURSIVE subordinate_tree AS (
SELECT employee_id, name, manager_id, 1 AS level
FROM employees
WHERE employee_id = ? -- 初始员工的ID
UNION ALL
SELECT e.employee_id, e.name, e.manager_id, st.level + 1
FROM employees e
JOIN subordinate_tree st ON e.manager_id = st.employee_id
)
SELECT * FROM subordinate_tree;
在这个查询中:
- 非递归部分选择了初始员工(通过WHERE employee_id = ?指定)。
- 递归部分通过JOIN操作将employees表与上一次递归的结果集subordinate_tree连接起来。连接条件是员工的manager_id等于上一次递归结果集中的employee_id。
- 递归会一直进行,直到没有更多的下属可以添加到结果集中为止。每次递归只使用上一次递归的结果集作为输入,而不是前面所有递归的结果集。
注意:递归查询从初始的非递归部分开始,初试的非递归部分查出的结果作为首次递归查询的条件,而且此后每次递归只会把上次递归产生的结果作为查询的匹配条件,而不是此前所有递归的结果作为查询的匹配条件,当上次递归查询的结果为空,则递归查询结束(若没有显式的写出递归结束条件的话)
标签:语句,name,递归,查询,depth,SQL,department,id From: https://blog.csdn.net/ansmallwhite/article/details/136958166