首页 > 数据库 >SQL语句的递归查询

SQL语句的递归查询

时间:2024-03-23 09:04:50浏览次数:25  
标签:语句 name 递归 查询 depth SQL department id

       在银行的统计分析任务中,往往是需要查询本行及其下级行、下级行的支行等各机构各自的运营情况,入参可以能是总行,也可能是一级行或二级行甚至支行,如果针对每种情况都各种写一个查询语句,工作量过于繁杂,但用了递归查询,就可以一劳永逸了;

    下面介绍一下递归查询的格式:

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;

在这个查询中:

  1. 非递归部分选择了初始员工(通过WHERE employee_id = ?指定)。
  2. 递归部分通过JOIN操作将employees表与上一次递归的结果集subordinate_tree连接起来。连接条件是员工的manager_id等于上一次递归结果集中的employee_id。
  3. 递归会一直进行,直到没有更多的下属可以添加到结果集中为止。每次递归只使用上一次递归的结果集作为输入,而不是前面所有递归的结果集。

         注意:递归查询从初始的非递归部分开始,初试的非递归部分查出的结果作为首次递归查询的条件,而且此后每次递归只会把上次递归产生的结果作为查询的匹配条件,而不是此前所有递归的结果作为查询的匹配条件,当上次递归查询的结果为空,则递归查询结束(若没有显式的写出递归结束条件的话)

标签:语句,name,递归,查询,depth,SQL,department,id
From: https://blog.csdn.net/ansmallwhite/article/details/136958166

相关文章

  • django《大学计算机》课程思政资源共享平台(源码+mysql+论文)
    本系统(程序+源码)带文档lw万字以上 文末可获取本课题的源码和程序系统程序文件列表系统的选题背景和意义选题背景:在当今信息化社会,教育领域正逐渐实现数字化转型,其中课程资源的共享与利用成为提高教学效率和质量的关键。特别是对于《大学计算机》这类基础且重要的课程,构......
  • django+Mybatis的医生在线诊所平台(源码+mysql+论文)
    本系统(程序+源码)带文档lw万字以上 文末可获取本课题的源码和程序系统程序文件列表系统的选题背景和意义选题背景:随着互联网技术的迅猛发展和普及,越来越多的传统行业开始向数字化转型。医疗健康领域作为与人们生活密切相关的行业,其服务模式也正逐渐从传统的面对面诊疗转......
  • djangoJAVA汽车年审管理系统(源码+mysql+论文)
    本系统(程序+源码)带文档lw万字以上 文末可获取本课题的源码和程序系统程序文件列表系统的选题背景和意义选题背景:随着汽车产业的快速发展,汽车已经成为人们日常生活中不可或缺的交通工具。然而,随着汽车数量的增加,汽车安全问题也日益凸显。为了确保道路交通安全,各国政府都......
  • 【附源码】django计算机毕业设计web的房屋租赁系统的设计与实现(源码+mysql+论文)
    本系统(程序+源码)带文档lw万字以上 文末可获取本课题的源码和程序系统程序文件列表系统的选题背景和意义选题背景:随着经济的发展和社会的进步,人们对于居住环境的需求越来越高。房屋租赁市场作为房地产市场的重要组成部分,近年来呈现出快速发展的态势。然而,传统的房屋租赁......
  • if、switch语句构成的选择结构详解
    前言:C语言是结构化的程序设计语言,这里的结构指的是顺序结构、选择结构、循环结构,C语言是能够实现这三种结构的,其实我们如果仔细分析,我们日常所见的事情都可以拆分为这三种结构或者这三种结构的组合,下文将讲述C语言中选择结构相关语句语法。一、if语句if语句分为单if语句、if......
  • mysql常规优化
    1、通过慢查日志等定位那些执行效率较低的SQL语句2、explain分析SQL的执行计划需要重点关注type、rows、filtered、extra。type由上至下,效率越来越高ALL全表扫描index索引全扫描range索引范围扫描,常用语<,<=,>=,between,in等操作ref使用非唯一索引扫描或唯一索引前缀......
  • .lastUpdated:The POM for mysql:mysql-connector-java:jar:8.1.0 is missing, no depe
    描述:在IDEA中,出现该类报错,查看本地仓库中项目对应的jar包存在,却无法获取时,可能是文件中生成.lastUpdated文件或有remote.repositories文件导致的。.lastUpdated:在更新maven项目的时候,每一个jar包路径下的_remote.repositories文件都会同setting.xml中设置的仓库地址id......
  • 房屋租赁系统(JSP+java+springmvc+mysql+MyBatis)
    本项目包含程序+源码+数据库+LW+调试部署环境,文末可获取一份本项目的java源码和数据库参考。项目文件图项目介绍随着城市化进程的加快和人口流动性增大,房屋租赁市场日益繁荣,对租赁信息的管理提出了更高要求。一个高效的房屋租赁系统能够为房东和租户提供一个便捷的信息发布......
  • [项目] Java + Servlet + MySql + BootStrap4 一个简单的购书网(网上书城)项目 (附源码)
    ......
  • mysql感悟
    https://javaguide.cn/database/sql/sql-questions-01.html小总结using()函数适用联表字段一致情况的joinhaving能使用select别名筛选SELECTorder_num,COUNT(*)ASitemsFROMOrderItemsGROUPBYitemsHAVINGCOUNT(*)>=3ORDERBYitems,order_num;mysqlhaving别......