时间戳

首页 > 解决方案 > 如何按升序对包含数字和字母数字值的 SQL 行进行排序？

sql - 如何按升序对包含数字和字母数字值的 SQL 行进行排序？

问题描述

我的 SQL 查询返回了以下行 - 有人可以帮助使用通用排序代码按升序对值进行排序吗？

('High_Speed'),
('M1 Speed'),
('M13 Speed'),
('M14 Speed'),
('M2 Speed'),
('M3 Speed'),
('Medium_Speed'),
('Test1 zone1 High_Speed'),
('Test1 zone11 High_Speed'),
('Test1 zone2 High_Speed'),
('Test1 zone21 High_Speed'),
('Medium_Speed'),
('Zone206 Speed')

预期排序-

('High_Speed'),
('M1 Speed'),
('M2 Speed'),
('M3 Speed'),
('M13 Speed'),
('M14 Speed'),
('Medium_Speed'),
('Test1 zone1 High_Speed'),
('Test1 zone2 High_Speed'),
('Test1 zone11 High_Speed'),
('Test1 zone21 High_Speed'),
('Medium_Speed'),
('Zone206 Speed')

标签： sqlsortingalphanumeric

解决方案

最通用的方法是使用case表达式：

order by (case col
            when 'High_Speed' then 1
            when 'M1 Speed' then 2
            when 'M2 Speed' then 3
            when 'M3 Speed' then 4
            when 'M13 Speed' then 5
            when 'M14 Speed' then 6
            when 'Medium_Speed' then 7
            when 'Test1 zone1 High_Speed' then 8
            when 'Test1 zone2 High_Speed' then 9
            when 'Test1 zone11 High_Speed' then 10
            when 'Test1 zone21 High_Speed' then 11
            when 'Medium_Speed' then 12
            when 'Zone206 Speed' then 13
            else 999
          end)

推荐阅读