在mysql中按时间进行排序和编号

Question

我有未排序的时钟。我想对这些小时进行排序，并在字段中插入序列号。如何在程序中执行此操作？谢谢

例如：

示例表：

id, time_, row_number
1,'07:57:01',0
2,'07:55:01',0
3,'08:01:21',0
4,'08:05:51',0
5,'08:04:11',0
6,'08:09:21',0

我想要的结果：

id, time_, row_number
1,'07:57:01',2
2,'07:55:01',1
3,'08:01:21',3
4,'08:05:51',5
5,'08:04:11',4
6,'08:09:21',6

SQLFIDDLE

排名如下：Select * from exampletable order by row_number;

id, time_, row_number
2,'07:55:01',1
1,'07:57:01',2    
3,'08:01:21',3
5,'08:04:11',4
4,'08:05:51',5
6,'08:09:21',6

如何在程序中获得此订单？谢谢

Answer 1

UPDATE exampletable t1
JOIN ( SELECT id, ROW_NUMBER() OVER (ORDER BY time_ ASC) row_numer
FROM exampletable ) t2 USING (id)
SET t1.row_numer = t2.row_numer;

https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=130beaf4c22fbd7d37cfd65a4aee306f

列time_未定义为唯一的，因此它可能包含重复的值，并且未定义此类数据集的输出。例如，您可能必须将排序表达式扩展为ORDER BY time_ ASC, id ASC，或者使用RANK()/DENSE_RANK()而不是ROW_NUMBER()。

Answer 2

我刚开始学习mysql，但如果你有mysql 8我认为这应该工作

-- ranking by time (window function)
select 
id,time_, rank() over (order by time_ ) 
from 
exampletable 
order by 
id asc; -- in ascending

id  time_   rank() over (order by time_ )
1   07:57:01    2
2   07:55:01    1
3   08:01:21    3
4   08:05:51    5
5   08:04:11    4
6   08:09:21    6

这也适用于mariadbhttps://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=4e3345276384189d979409106b77eabee