【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)
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容