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 等的顺序排列。我怎样才能使 23 小时位于顶部，然后是 00,01等等

Answer 1

尝试使用HOUR(dt + INTERVAL 1 HOUR)而不是HOUR(hm) DESC在你的ORDER BY条款。