SQL-递归查询

大家好，欢迎来到IT知识分享网。

运行环境：

Mysql8以上，递归查询功能在8以上版本被正式引入

一、SQL递归查询的概念

        递归指的是通过调用函数或过程或自身来解决问题的方法，常用于一些具有规律性循环的操作。SQL递归查询是基于一组初始数据，通过递归查询，返回符合条件的数据集。

        白话文：在SQL中遇到循环相关的查询操作，但又不想使用自定义函数和存储过程，考虑使用递归查询。

二、SQL递归查询的一般形式

 -- RECURSIVE 是必要关键字 -- 将第一次查询的结果赋值给了临时表，然后将临时表和递归表做双表联查 WITH RECURSIVE temp (col1, col2, ..., coln) AS ( -- 递归部分 -- 第一次查询结果 SELECT init_table.col1, init_table.col2, ..., init_table.coln FROM init_table UNION ALL SELECT temp.col1, temp.col2, ..., temp.coln FROM temp , init_table WHERE temp.condition -- 终止条件 ) -- 终止条件部分 SELECT * FROM temp ;

三、案例

1、已知SQL字段中存在数字3，需纵向扩展为1，2，3.具体相关如下图所示：

数据源表t1

目标效果

分析：明显需要使用到循环或sequence，再使用炸裂函数

方案一：通过调用自定义函数返回一组数据结果，例如：3变成[‘1′,’2′,’3’]，再用炸裂函数得到多行数据。很麻烦，暂未实现。

方案二：Mysql使用递归查询

方案三：Oracle使用rownum

环境准备SQL

create table emp2(col1 varchar(20), col2 int); insert into emp2 values ('A', 2), ('B', 3), ('C', 4);

Mysql解法：递归查询

# 递归查询 with recursive t1 AS ( -- 查询 第一次的结果 select * from emp2 a union all -- 当临时表的id不为1时，查询出临时表的col2-1，和之前的数据进行union all select b.col1,t1.col2-1 from emp2 b join t1 on b.col1=t1.col1 where t1.col2<>1 ) select * from t1 order by col1,col2;

Oracle解法：rownum

select t1.col1, t2.num from t1 ,(select rownum num from dual connect by rownum <= (select max(col2) from t1) )t2 where t1.col2>t2.num 

参考资料：

MySQL递归查询超详细–保姆级别讲解-CSDN博客