SQL 版本号排序

SQL 语句直接对内容为版本号格式的字段进行排序时，排序效果通常不是最终想要的效果，因为最终需要的效果，是需对版本号里的每一段（通常以小数点分隔）按数值进行排序。
解决这个问题，主要借助 SUBSTRING_INDEX(str, delim, count) 函数分段提取，再结合结合 CAST() 函数进行类型转换，转换成数值并排序。
SUBSTRING_INDEX 函数从 str 的开始位置取值，由 delim 指定分隔符，取第 count 个分隔符前面部分的内容，其中 count 为负数时，从末尾取值。在嵌套使用的时候，可以指定单独取原始顺序的第2段、第3段等，可以取任意连续1至N段。
例如：

1. SELECT SUBSTRING_INDEX('192.168.10.15', '.', 1) AS v
2. UNION SELECT SUBSTRING_INDEX('192.168.10.15', '.', 2)
3. UNION SELECT SUBSTRING_INDEX('192.168.10.15', '.', 3)
4. UNION SELECT SUBSTRING_INDEX('192.168.10.15', '.', 4);

复制代码

效果：

 
 
嵌套使用 SUBSTRING_INDEX() 函数，并结合 CAST() 函数将结果转换为数值排序：

1. SELECT v,
2. CAST(SUBSTRING_INDEX(v, '.', 1) AS UNSIGNED) AS v1,
3. CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(v, '.', 2), '.', -1) AS UNSIGNED) AS v2,
4. CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(v, '.', 3), '.', -1) AS UNSIGNED) AS v3,
5. CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(v, '.', 4), '.', -1) AS UNSIGNED) AS v4
6. FROM (
7.     SELECT '6.3.5.5' AS v
8.     UNION SELECT '10.1.1.8'
9.     UNION SELECT '1.2.3.17'
10.     UNION SELECT '1.12.3.6'
11.     UNION SELECT '1.12.250.5'
12.     UNION SELECT '256.12.3.4'
13. ) AS t
14. ORDER BY v1 DESC, v2 DESC, v3 DESC, v4 DESC;

复制代码

效果：

若版本号只有3段的，则可以去掉上述 v4 部分。
另外，也可以不需要添加到查询的列里，直接放在 ORDER BY 子句中：

1. SELECT v
2. FROM (
3.     SELECT '6.3.5.5' AS v
4.     UNION SELECT '10.1.1.1'
5.     UNION SELECT '1.2.3.4'
6.     UNION SELECT '1.12.3.4'
7.     UNION SELECT '1.12.250.4'
8.     UNION SELECT '256.12.3.4'
9. ) AS t
10. ORDER BY
11. CAST(SUBSTRING_INDEX(v, '.', 1) AS UNSIGNED) DESC,
12. CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(v, '.', 2), '.', -1) AS UNSIGNED) DESC,
13. CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(v, '.', 3), '.', -1) AS UNSIGNED) DESC,
14. CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(v, '.', 4), '.', -1) AS UNSIGNED) DESC;

复制代码

效果：

 
 
 另外，结合 INET_ATON(str) 函数可以把输入参数按IP地址格式排序，但是如果存在其中一段的数值超过255的记录，则排序对该记录不生效或结果不符合预期；若可以保证每一段均不超过255，则可以直接使用该函数辅助排序，省事。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！