Mysql 单字段排序形成连续序列
2023-09-14 09:13:14 时间
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的数据。
相关文章
- MySQL中如何删除表(mysql怎么删除表)
- MySQL数据库重启:一种新方式(mysql重启数据库)
- 查看MySQL安装路径:快速操作指南(查询mysql安装路径)
- 探索MySQL的端口号之旅(查看mysql端口号)
- 使用MySQL嵌套查询实现复杂操作(mysql查询嵌套)
- 设置MySQL存储路径:一步一步的指南(mysql设置存储路径)
- MySQL: 利用首字母排序解决数据检索问题(mysql首字母排序)
- MySQL集成开发环境:改变开发体验(mysql集成开发环境)
- MySQL排序:实现自增的方法(mysql排序自增)
- 利用MySQL查询更新数据,实现数据精准管理(mysql查询更新数据)
- MySQL添加序列:探索无限可能(mysql添加序列)
- MySQL实现多次排序的简易方法(mysql多次排序)
- MySQL语句排序查询:实现快速结果(mysql语句排序查询)
- MySQL实现汉字拼音排序的方法(mysql汉字拼音排序)
- MySQL汉字拼音排序技巧及实现(mysql汉字拼音排序)
- MySQL实现汉字拼音排序技术简介(mysql汉字拼音排序)
- MySQL数据库文件的.frm后缀介绍(mysql数据库文件后缀)
- MySQL使用详解:全面学习MYSQL技术(mysql大全)
- 号MySQL:探索序列号背后的故事(mysql 序列)
- MySQL查询之外键技术精要(mysql查询外键)
- MySQL实现距离排序的实现方法(mysql距离排序)
- 求和计算MySQL中如何对两列求和(mysql 中两列总和)
- 称MySQL连接示例用C语言轻松实现(c mysql连接实例名)
- 探究MySQL中DEC数据类型的使用及其优劣势(mysql 中dec)
- MySQL如何进行两表关联排序(mysql两表关联排序)
- 使用CAS与MySQL实现可靠连接(cas mysql连接)
- MySQL如何使用ASP插入数据(asp插入数据mysql)
- ABP 框架连接 MySQL 数据库实现数据存取(abp链接mysql)
- 5天玩转MySQL,迎来下载新乐章(5天玩转mysql下载)
- MySQL查询如何使用升序和降序排序语句(mysql中升序降序语句)
- MySQL中如何查询前三行(mysql中前3行)
- 详解Mysql创建视图的步骤(mysql中创建视图过程)
- 如何解决MySQL出现的段错误问题(mysql_ 段错误)
- MySQL降序排列实现方法详解(mysql下降排序)
- MySQL排序不一定是按ID顺序(mysql 不是id排序)