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;
执行结果如下
实际开发中用到的例子
// 使用的是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