SQL 时间列不按升序排序 [英] SQL time column does not sort in ascending order
本文介绍了SQL 时间列不按升序排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在尝试编写一个按时间列排序的 SQL 语句时遇到了一些麻烦,该列是 varchar 格式的升序.这是我的 SQL 语句:
I was having some trouble when trying to write an SQLstatement which sorted by time column which is in varchar format in ascending order. Here is my SQL statement:
SELECT mrtpopTime, mrtpopAmt
FROM tm_mrtpop
WHERE mrtpopName = ''
ORDER BY mrtpopTime
我得到了这些结果:
从图片中可以看出,它是按字符排序的.反正有没有像这样排序:
As you can see from the picture, it was sorted by character by character. Is there anyway to sort it like:
0:00, 1:00, 2:00, 3:00 all the way to 23:00
有什么想法吗?提前致谢.
Any ideas? Thanks in advance.
推荐答案
将 varchar 时间列转换为 time
(或虚拟日期加时间)并按此排序:
Convert varchar time column to a time
(or dummy date plus time) and order by that:
SELECT mrtpopTime, mrtpopAmt
FROM tm_mrtpop
WHERE mrtpopName = ''
ORDER BY STR_TO_DATE(mrtpopTime, '%H:%i')
这篇关于SQL 时间列不按升序排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文