MySQL8.x WITH

标题递归查询 with recursive

查看数据库版本

select version();

我司正式测试MySQL数据库都已经是8.x版本，数据库更新的同时也有一些新特性比如递归查询

派生表:它将子查询做为一个表来处理，这个由子查询得出的新表就是我们说的“派生表”。

a表就是派生表

SELECT * FROM (SELECT  name,username FROM `user` WHERE username="13608031977") a;

with 也可以用来生成派生表 下面两句一起执行

WITH b AS (SELECT  name,username FROM `user` WHERE username="13608031977")
SELECT * FROM b;

它有自己的名称：CTE(Common Table Expressions)通用表表达式

– 为b表两列取别名

WITH b(id,id1) AS (SELECT  name,username FROM `user` WHERE username="13608031977")
SELECT * FROM b;

 如果某个 CTE 在子查询中引用了自己，就称为递归 CTE WITH子句必须使用WITH RECURSIVE

WITH RECURSIVE cte (n) AS -- RECURSIVE [数] 递归的；循环的（有道）
(
  SELECT 1 -- 初始值
  UNION ALL  -- 上下连接
  SELECT n + 1 FROM cte WHERE n < 5  -- 递归条件 
)
SELECT * FROM cte;

 未限制无限递归 默认递归次数为1000次

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT * FROM cte;
-- 可以通过下面语句改变递归次数
SET SESSION cte_max_recursion_depth = 1000000;

-- 应用场景1 上级部门归属查询

-- SELECT department_id FROM `user` WHERE id=89010;
with recursive department_path(id,path) as
(
SELECT department_id,department_id FROM `user` WHERE username="13608032003"
union ALL
SELECT b.id,b.p_id FROM department_path a
LEFT JOIN department b ON 1=1
WHERE  b.id=a.path AND a.path<>0 
)
SELECT * FROM department_path;

应用场景二 车辆gps是否在线

	
WITH recursive Date_Ranges(Datetime) AS (
-- 条件初始化 
    select '2022-04-21 00:00:00' 
-- union all 上下连接所有结果集
   union all
-- 条件递增规则 
   select Datetime + interval 10 minute
   from Date_Ranges
-- 条件结束
   where Datetime < '2022-04-21 23:49:59')
-- 虚拟表创建完成

-- 查看虚拟表
select * from Date_Ranges

left join (select FROM_UNIXTIME(floor(time/600)*600) as use_time,plate_num from `vehicle_point_2022-04-21` 
where plate_num='川AAE383'
group by  use_time) b on DateTime=b.use_time