MYSQL8.0 WITH RECURSIVE递归查询

落日映苍穹つ 2024-04-07 13:45 13阅读 0赞

MYSQL 8.0 版本以上 使用 WITH RECURSIVE 实现递归

**注意：**写法比较简单，也比较灵活，但是只适用于**MySQL8.0**及以上版本，这种写法其实和 PostgreSQL 的写法是一样的。

WITH RECURSIVE 语法

WITH recursive 表名 AS ( 
    	初始语句（非递归部分） 
    	UNION ALL 
    	递归部分语句
    )
    [ SELECT| INSERT | UPDATE | DELETE]

with recursive 由两部分组成。第一部分是非递归部分（ [union][] all 上方），第二部分是递归部分（union all下方）。递归部分第一次进入的时候使用非递归部分传递过来的参数，也就是第一行的数据值，进而得到第二行数据值，然后根据第二行数据值得到第三行数据值。

**用法示例**

定义下面这样的表，存储每个区域（省、市、区）的 id，名字及上级区域的 pid

create table tb(id varchar(3) , pid varchar(3) , name varchar(10)); 
     
    insert into tb values('002' , 0 , '浙江省'); 
    insert into tb values('001' , 0 , '广东省'); 
    insert into tb values('003' , '002' , '衢州市');  
    insert into tb values('004' , '002' , '杭州市') ; 
    insert into tb values('005' , '002' , '湖州市');  
    insert into tb values('006' , '002' , '嘉兴市') ; 
    insert into tb values('007' , '002' , '宁波市');  
    insert into tb values('008' , '002' , '绍兴市') ; 
    insert into tb values('009' , '002' , '台州市');  
    insert into tb values('010' , '002' , '温州市') ; 
    insert into tb values('011' , '002' , '丽水市');  
    insert into tb values('012' , '002' , '金华市') ; 
    insert into tb values('013' , '002' , '舟山市');  
    insert into tb values('014' , '004' , '上城区') ; 
    insert into tb values('015' , '004' , '下城区');  
    insert into tb values('016' , '004' , '拱墅区') ; 
    insert into tb values('017' , '004' , '余杭区') ; 
    insert into tb values('018' , '011' , '金东区') ; 
    insert into tb values('019' , '001' , '广州市') ; 
    insert into tb values('020' , '001' , '深圳市') ;

需要查出某个省，如浙江省，管辖的所有市及市辖地区，示例 sql 如下

with RECURSIVE cte as
    (
        select a.id, a.name from tb a where id = '002'
        union all 
        select k.id, k.name from tb k inner join cte c on c.id = k.pid
    ) 
    select id, name from cte;

执行结果如下

![e7b383341393478f9c3d515b4950291f.png][]

实际开发中用到的例子

// 使用的是postgre，遍历设备树获取指定设备树和所有子节点的测点信息

WITH RECURSIVE tree AS (
    	SELECT * FROM eqp_tree 
    	WHERE del_flag = 0 AND node_code = #{eqpCode}
    	UNION
    	SELECT e.* FROM eqp_tree e INNER JOIN tree s ON e.parent_code = s.node_code AND e.del_flag = 0 
    	) SELECT
    	k2.id,
    	k2.eqp_code,
    	k2.mp_code,
    	k2.mp_name,
    	k2.measure_no,
    	k2.measureParameters,
    	k3.location_code,
    	k3.location_name 
    FROM
    	tree k1
    	JOIN (
    	SELECT
    		A.id,
    		A.eqp_code,
    		A.mp_code,
    		A.mp_name,
    		A.measure_no,
    		A.measure_position,
    		array_to_string ( array_agg ( B.measure_parameter ), '|' ) AS measureParameters 
    	FROM
    		measure_point_info A
    		LEFT JOIN measure_point_samples_set B ON A.mp_code = B.mp_code 
    		AND B.del_flag = 0 
    	WHERE
    		A.del_flag = 0 
    	GROUP BY
    		A.id,
    		A.eqp_code,
    		A.mp_code,
    		A.mp_name,
    		A.measure_no,
    		A.measure_position 
    	) k2 ON k2.eqp_code = k1.node_code
    	LEFT JOIN configure_model_location K3 ON k3.location_code = k2.measure_position 
    	AND k3.del_flag = 0

上边 SQL 中用到了 postgresql 数据库实现某一列的数据拼接：array\_agg() ，摘录如下

postgre 中没有 **group\_concat** 函数，网上大部分的解决办法，都是自定义一个 group\_concat 函数。其实完全没必要，因为 postgre 中自带的 **array\_agg()** 函数完全可以实现该功能。postgre 中的 array\_agg(字段) 等价于mysql 中的 group\_concat(字段)。如果想将拼接的数据转换成字符串，可以在 array\_agg() 的基础上使用 array\_to\_string() 方法。即 **array\_to\_string(array\_agg(字段),',')**，其中 **','** 可以换成其他任意间隔符。

参考文章：[mysql 递归函数with recursive的用法\_cyan\_orange的博客-CSDN博客\_mysql recursive][mysql _with recursive_cyan_orange_-CSDN_mysql recursive]

[union]: https://so.csdn.net/so/search?q=union&spm=1001.2101.3001.7020
[e7b383341393478f9c3d515b4950291f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/084924a236e84fa9b39775ef05b27c47.png
[mysql _with recursive_cyan_orange_-CSDN_mysql recursive]: https://blog.csdn.net/mjfppxx/article/details/124879326