【MySQL】每日一题 2020-03-05
2020-03-05 本文已影响0人
每天要读书的Claire
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)
