Mysql 单字段排序形成连续序列

Mysql 单字段排序形成连续变化序列【待完善】

CREATE TABLE c (
  id int(10) NOT NULL,   
  name varchar(10) NOT NULL,
  start_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  end_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP  
)

insert into c 
values(1,'Lawson','2014-9-3','9999-12-31'),
(1,'Lawson','2018-3-7','9999-12-31'),
(1,'Lawson','2020-5-7','9999-12-31');

mysql> select * from c;
+----+--------+---------------------+---------------------+
| id | name   | start_date          | end_date            |
+----+--------+---------------------+---------------------+
|  1 | Lawson | 2014-09-03 00:00:00 | 9999-12-31 00:00:00 |
|  1 | Lawson | 2018-03-07 00:00:00 | 9999-12-31 00:00:00 |
|  1 | Lawson | 2020-05-07 00:00:00 | 9999-12-31 00:00:00 |
+----+--------+---------------------+---------------------+
3 rows in set (0.00 sec)



现在的需求是：如何将这个下一次的start_date，作为上一条数据的end_date。 但是最后一条记录的end_date不变。即，目标的实现结果如下：
mysql> select * from c;
+----+--------+---------------------+---------------------+
| id | name   | start_date          | end_date            |
+----+--------+---------------------+---------------------+
|  1 | Lawson | 2014-09-03 00:00:00 | 2018-03-07 00:00:00 |
|  1 | Lawson | 2018-03-07 00:00:00 | 2020-05-07 00:00:00 |
|  1 | Lawson | 2020-05-07 00:00:00 | 9999-12-31 00:00:00 |
+----+--------+---------------------+---------------------+

sql 1:
select 
*
from c c_1
inner join c c_2
on c_1.id = c_2.id;

+----+--------+---------------------+---------------------+----+--------+---------------------+---------------------+
| id | name   | start_date          | end_date            | id | name   | start_date          | end_date            |
+----+--------+---------------------+---------------------+----+--------+---------------------+---------------------+
|  1 | Lawson | 2014-09-03 00:00:00 | 9999-12-31 00:00:00 |  1 | Lawson | 2014-09-03 00:00:00 | 9999-12-31 00:00:00 |
|  1 | Lawson | 2018-03-07 00:00:00 | 9999-12-31 00:00:00 |  1 | Lawson | 2014-09-03 00:00:00 | 9999-12-31 00:00:00 |
|  1 | Lawson | 2020-05-07 00:00:00 | 9999-12-31 00:00:00 |  1 | Lawson | 2014-09-03 00:00:00 | 9999-12-31 00:00:00 |
|  1 | Lawson | 2014-09-03 00:00:00 | 9999-12-31 00:00:00 |  1 | Lawson | 2018-03-07 00:00:00 | 9999-12-31 00:00:00 |
|  1 | Lawson | 2018-03-07 00:00:00 | 9999-12-31 00:00:00 |  1 | Lawson | 2018-03-07 00:00:00 | 9999-12-31 00:00:00 |
|  1 | Lawson | 2020-05-07 00:00:00 | 9999-12-31 00:00:00 |  1 | Lawson | 2018-03-07 00:00:00 | 9999-12-31 00:00:00 |
|  1 | Lawson | 2014-09-03 00:00:00 | 9999-12-31 00:00:00 |  1 | Lawson | 2020-05-07 00:00:00 | 9999-12-31 00:00:00 |
|  1 | Lawson | 2018-03-07 00:00:00 | 9999-12-31 00:00:00 |  1 | Lawson | 2020-05-07 00:00:00 | 9999-12-31 00:00:00 |
|  1 | Lawson | 2020-05-07 00:00:00 | 9999-12-31 00:00:00 |  1 | Lawson | 2020-05-07 00:00:00 | 9999-12-31 00:00:00 |
+----+--------+---------------------+---------------------+----+--------+---------------------+---------------------+
9 rows in set (0.00 sec)
很明显，不满足要求，

sql 2:
select 
id,name,start_date,end_date
from (
	select 
	c_1.id,c_1.name,c_1.start_date,
	c_2.start_date as end_date
	from c c_1
	inner join c c_2
	on c_1.id = c_2.id
) t
where t.start_date < t.end_date;
+----+--------+---------------------+---------------------+
| id | name   | start_date          | end_date            |
+----+--------+---------------------+---------------------+
|  1 | Lawson | 2014-09-03 00:00:00 | 2018-03-07 00:00:00 |
|  1 | Lawson | 2014-09-03 00:00:00 | 2020-05-07 00:00:00 |
|  1 | Lawson | 2018-03-07 00:00:00 | 2020-05-07 00:00:00 |
+----+--------+---------------------+---------------------+
3 rows in set (0.00 sec)
但是发现多了一条2014-09-03 -> 2020-05-07 00:00:00的数据。少了一条2020-05-07 00:00:00 到9999-12-31 00:00:00的数据。

sql 3:
从表c_2中选择大于表c_1.start_date的最小的start_date
01.选择c_2.start_date 大于c_1.start_date。这个是一个结果集【可能会用到group by】
02.再从结果集中选择最小值。【可能会用到min()函数】


select
    c_1.id,
    c_1.start_date,
    min(c_2.start_date) AS end_date 
from c c_1
    join c c_2
    on((c_1.id = c_2.id))
where (c_1.start_date < c_2.start_date) 
group by c_1.id,c_1.start_date ;

+----+---------------------+---------------------+
| id | start_date          | end_date            |
+----+---------------------+---------------------+
|  1 | 2014-09-03 00:00:00 | 2018-03-07 00:00:00 |
|  1 | 2018-03-07 00:00:00 | 2020-05-07 00:00:00 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)

但是我们发现少了一条2020-05-07 到9999-12-31的数据。