递归查询子元素
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;