备注:测试数据库版本为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)