按时间(小时)排序mysql

Question

我试图弄清楚如何使用以下数据集按时间进行排序。

hm,total_count,avg_count,max_count
-------------------------------------
'23:15','17','5.6667','9'
'23:45','19','3.1667','5'
'06:15','13','6.5000','9'
'05:15','22','4.4000','7'
'05:45','12','6.0000','11'
'04:15','22','4.4000','6'
'04:45','14','4.6667','7'
'03:45','24','2.4000','5'
'02:45','82','5.4667','13'
'01:45','98','6.1250','13'
'00:45','59','4.2143','11'

我的查询：

SELECT DATE_FORMAT(`dt`, '%H:%i') as hm, SUM(`counts`) AS total_count, 
       AVG(`counts`) AS avg_count, MAX(`counts`) AS max_count 
FROM pax_load_distribution , plans 
WHERE `pax_load_distribution`.`plan_id` = `plans`.`id` AND 
      `plans`.`dt` BETWEEN '2017-05-01' AND '2017-05-31' 
GROUP BY hm 
ORDER BY HOUR(hm) DESC, MINUTE(hm) ASC;

，但是如您所见，查询在最高的23小时内组织了23,00,01,02等。等。

Answer 1

尝试在您的ORDER BY子句中使用HOUR(dt + INTERVAL 1 HOUR)而不是HOUR(hm) DESC。

Answer 2

您可以尝试：ORDER BY (HUOUR(hm) * 60 + MINUTE(hm)) ASC