【MySQL】每日一题 2020-03-05

mysql> select * from test_0305;
+------+-------+------------+
| code | invtp | date1      |
+------+-------+------------+
| 1001 | A     | 2018-01-01 |
| 1001 | B     | 2018-03-02 |
| 1001 | C     | 2018-04-01 |
| 1002 | AA    | 2018-01-01 |
| 1002 | BB    | 2018-02-28 |
| 1003 | CC    | 2018-01-01 |
+------+-------+------------+

mysql> select code,invtp,date1 as start_date ,coalesce(lead(date1)over(partition by code),'3000-12-31') as end_date from test_0305;
+------+-------+------------+------------+
| code | invtp | start_date | end_date   |
+------+-------+------------+------------+
| 1001 | A     | 2018-01-01 | 2018-03-02 |
| 1001 | B     | 2018-03-02 | 2018-04-01 |
| 1001 | C     | 2018-04-01 | 3000-12-31 |
| 1002 | AA    | 2018-01-01 | 2018-02-28 |
| 1002 | BB    | 2018-02-28 | 3000-12-31 |
| 1003 | CC    | 2018-01-01 | 3000-12-31 |
+------+-------+------------+------------+
6 rows in set (0.01 sec)