sql中 with recursive和with as的用法

sql中with xxxx as () 是对一个查询子句做别名，同时数据库会对该子句生成临时表；

with recursive 则是一个递归的查询子句，他会把查询出来的结果再次代入到查询子句中继续查询，如下面的语句



WITH RECURSIVE d(n, fact) AS (
VALUES (0, 1)
UNION ALL
SELECT n+1, (n+1)*fact FROM d WHERE n < 7
)
SELECT * FROM d

结果如下：

"n=0; fact=1",
"n=1; fact=1",
"n=2; fact=2",
"n=3; fact=6",
"n=4; fact=24",
"n=5; fact=120",
"n=6; fact=720",
"n=7; fact=5040"

---

例如利用with recursive来模拟生成表数据

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, '2020-07-23 00:01: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 < '2020-07-23 10:00:00'
)
select * from s;

internal的用法参考这里

sql中interval用法总结_端木胥的博客-CSDN博客_interval sql

---

WITH RECURSIVE 递归 与with as 子查询部分 - Johnson718 - 博客园with as 子查询与with recursive as 递归_王营90的博客-CSDN博客WITH RECURSIVE 递归 与with as 子查询部分 - Johnson718 - 博客园

实战 SQL：亚马逊、京东等电商平台的销售排行榜和飙升榜