mysql8递归查询

递归查询子元素

create table t_test(
	id varchar(64) not null primary key,
	name varchar(64),
	pid varchar(64)
);
INSERT INTO `t_test`(`id`, `name`, `pid`) VALUES ('1', '张三', '0');
INSERT INTO `t_test`(`id`, `name`, `pid`) VALUES ('2', '张三儿子', '1');
INSERT INTO `t_test`(`id`, `name`, `pid`) VALUES ('3', '张三孙子', '2');
INSERT INTO `t_test`(`id`, `name`, `pid`) VALUES ('4', '李四', '0');
INSERT INTO `t_test`(`id`, `name`, `pid`) VALUES ('5', '赵武', '0');

-- 把 张三 以及 张三的子孙查询出来
with recursive tmp as(
	select a.* from t_test a where a.name = '张三'
	union all
	select a.* from t_test a, tmp t where t.id = a.pid
)
select * from tmp;

递归查询时间段

with recursive date_range as (
	select '2022-02-02 00:00:00' as dateTime
	union all
	select dateTime + interval 1 day from date_range where dateTime < '2022-02-04 00:00:00'
)
select * from date_range

带参数写法：

with recursive date_range(jt_date) as (
select @num:='2022-08-11'
union all
select @num:=date_add(@num, interval 1 day) from date_range where jt_date < '2022-08-17'
)
select * from  date_range;

interval 1 day : 间隔一天
interval 1 hour : 间隔一小时
interval 1 minute : 间隔一分钟
interval 1 second : 间隔一秒钟

平方数列

with recursive square(x, f) as(
	select 1, 1
	union all
	select x + 1, (x + 1) * (x + 1) from square where x < 8
)
select * from square;

斐波那契数列

方法定义：F(1)=1，F(2)=1, F(n)=F(n-1)+F(n-2)（n>=3，n∈N*）

with recursive fibonacci(x, f1, f2) as(
	select 1, 1, 1
	union all
	select x + 1, f2, f1 + f2 from fibonacci where x < 8
)
select x, f1, f2 from fibonacci;