mysql 插入预计_MySQL 生成简单的预测

备注:测试数据库版本为MySQL 8.0

测试数据:

drop table t10;

create table t10(id int,order_date date,process_date date);

insert into t10 values (1,'2020-09-25','2020-09-27');

insert into t10 values (2,'2020-09-26','2020-09-28');

insert into t10 values (3,'2020-09-27','2020-09-29');

一.需求

以当前数据为基础，返回另外的行和列，用于表示未来活动。

例如，查看下列结果集:

+------+------------+--------------+

| id | order_date | process_date |

+------+------------+--------------+

| 1 | 2020-09-25 | 2020-09-27 |

| 1 | 2020-09-26 | 2020-09-28 |

| 1 | 2020-09-27 | 2020-09-29 |

+------+------------+--------------+

要求对于结果集中的每一行，都要返回3行(对于一个订单，原来有一行再另外加两行)；

此外，还需要另外增加两列，用于存放对订单做进一步处理的日期。

从上面的结果集中可以看到，订单处理需要两天。对于这个例子，假定订单处理之后进行核对，最后一步是出货；

订单处理完1天后进行核对，核对完后过1天就出货。

显然希望能够将上面的结果集转换为如下结果集:

+------+------------+--------------+----------+----------+

| id | order_date | process_date | verified | shipped |

+------+------------+--------------+----------+----------+

| 1 | 2020-09-25 | 2020-09-27 | NULL | NULL |

| 1 | 2020-09-25 | 2020-09-27 | 20200928 | NULL |

| 1 | 2020-09-25 | 2020-09-27 | 20200928 | 20200929 |

| 2 | 2020-09-26 | 2020-09-28 | NULL | NULL |

| 2 | 2020-09-26 | 2020-09-28 | 20200929 | NULL |

| 2 | 2020-09-26 | 2020-09-28 | 20200929 | 20200930 |

| 3 | 2020-09-27 | 2020-09-29 | NULL | NULL |

| 3 | 2020-09-27 | 2020-09-29 | 20200930 | NULL |

| 3 | 2020-09-27 | 2020-09-29 | 20200930 | 20200931 |

+------+------------+--------------+----------+----------+

二.解决方案

这里的关键是用笛卡尔积为每个订单生产两个额外行，然后，只要使用case表达式创建所需要的列值就可以了。

with recursive nrows(n) as (

select 1

union all

select n+ 1

from nrows

where n+1 <= 3

)

select id,

order_date,

process_date,

case when nrows.n >= 2

then process_date + 1

else null

end as verified,

case when nrows.n = 3

then process_date +2

else null

end as shipped

from t10 orders, nrows

order by 1;

测试记录:

mysql> with recursive nrows(n) as (

-> select 1

-> union all

-> select n+ 1

-> from nrows

-> where n+1 <= 3

-> )

-> select id,

-> order_date,

-> process_date,

-> case when nrows.n >= 2

-> then process_date + 1

-> else null

-> end as verified,

-> case when nrows.n = 3

-> then process_date +2

-> else null

-> end as shipped

-> from t10 orders, nrows

-> order by 1;

+------+------------+--------------+----------+----------+

| id | order_date | process_date | verified | shipped |

+------+------------+--------------+----------+----------+

| 1 | 2020-09-25 | 2020-09-27 | NULL | NULL |

| 1 | 2020-09-25 | 2020-09-27 | 20200928 | NULL |

| 1 | 2020-09-25 | 2020-09-27 | 20200928 | 20200929 |

| 2 | 2020-09-26 | 2020-09-28 | NULL | NULL |

| 2 | 2020-09-26 | 2020-09-28 | 20200929 | NULL |

| 2 | 2020-09-26 | 2020-09-28 | 20200929 | 20200930 |

| 3 | 2020-09-27 | 2020-09-29 | NULL | NULL |

| 3 | 2020-09-27 | 2020-09-29 | 20200930 | NULL |

| 3 | 2020-09-27 | 2020-09-29 | 20200930 | 20200931 |

+------+------------+--------------+----------+----------+

9 rows in set (0.00 sec)