mysql递归函数

阿花落知多少

已于 2023-07-21 01:59:48 修改

阅读量186

点赞数

文章标签：数据库

于 2023-07-10 15:14:10 首次发布

本文链接：https://blog.csdn.net/qq_39940205/article/details/131639665

版权

创建了一个包含产品信息的示例表，包括产品ID、名称、子类别和类别。接着插入了手机和家电产品的数据。然后，建立了销售表并生成了模拟的销量数据。此外，展示了使用递归生成斐波那契数列和1-100的和，以及如何在数据库中递归获取菜单全路径。

摘要由CSDN通过智能技术生成

-- 创建示例表
create table products(
  product_id integer not null primary key,
  product_name varchar(100) not null unique,
  product_subcategory varchar(100) not null,
  product_category varchar(100) not null
);

insert into products values(1, 'iPhone 11', '手机', '手机通讯');
insert into products values(2, 'HUAWEI P40', '手机', '手机通讯');
insert into products values(3, '小米10', '手机', '手机通讯');
insert into products values(4, 'OPPO Reno4', '手机', '手机通讯');
insert into products values(5, 'vivo Y70s', '手机', '手机通讯');
insert into products values(6, '海尔BCD-216STPT', '冰箱', '大家电');
insert into products values(7, '康佳BCD-155C2GBU', '冰箱', '大家电');
insert into products values(8, '容声BCD-529WD11HP', '冰箱', '大家电');
insert into products values(9, '美的BCD-213TM(E)', '冰箱', '大家电');
insert into products values(10, '格力BCD-230WETCL', '冰箱', '大家电');
insert into products values(11, '格力KFR-35GW', '空调', '大家电');
insert into products values(12, '美的KFR-35GW', '空调', '大家电');
insert into products values(13, 'TCLKFRd-26GW', '空调', '大家电');
insert into products values(14, '奥克斯KFR-35GW', '空调', '大家电');
insert into products values(15, '海尔KFR-35GW', '空调', '大家电');

create table sales(
  product_id integer not null,
  sale_time timestamp not null,
  quantity integer not null
);

-- 生成模拟销量数据
insert into sales
with recursive s(product_id, sale_time, quantity) as (
  select product_id, '2022-04-01 00:00:00', floor(10*rand(0)) from products
  union all
  select product_id, sale_time + interval 1 minute, floor(10*rand(0))
  from s 
  where sale_time < '2022-04-01 10:00:00'
)
select * from s;

-- 斐波拉契数列
WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;

-- 1-100的和
WITH RECURSIVE t1 (num) AS
(
  SELECT 1
	UNION ALL
	SELECT num + 1
	  FROM t1
		WHERE num < 100
)
SELECT sum(num) FROM t1;

ruoyi菜单表递归出全路径

with recursive temp(id,name,pid,paths) as
       (
           select menu_id,menu_name,parent_id,cast(menu_name as char(1000)) paths from sys_menu where parent_id=0
           union all
           select s.menu_id,s.menu_name,s.parent_id,concat(paths,'>',s.menu_name) from temp t join sys_menu s on (t.id=s.parent_id)
       )
select * from temp order by id;