SQL 语句直接对内容为版本号格式的字段进行排序时,排序效果通常不是最终想要的效果,因为最终需要的效果,是需对版本号里的每一段(通常以小数点分隔)按数值进行排序。
解决这个问题,主要借助 SUBSTRING_INDEX(str, delim, count) 函数分段提取,再结合结合 CAST() 函数进行类型转换,转换成数值并排序。
SUBSTRING_INDEX 函数从 str 的开始位置取值,由 delim 指定分隔符,取第 count 个分隔符前面部分的内容,其中 count 为负数时,从末尾取值。在嵌套使用的时候,可以指定单独取原始顺序的第2段、第3段等,可以取任意连续1至N段。
例如:
SELECT SUBSTRING_INDEX(\'192.168.10.15\', \'.\', 1) AS v UNION SELECT SUBSTRING_INDEX(\'192.168.10.15\', \'.\', 2) UNION SELECT SUBSTRING_INDEX(\'192.168.10.15\', \'.\', 3) UNION SELECT SUBSTRING_INDEX(\'192.168.10.15\', \'.\', 4);
效果:
嵌套使用 SUBSTRING_INDEX() 函数,并结合 CAST() 函数将结果转换为数值排序:
SELECT v, CAST(SUBSTRING_INDEX(v, \'.\', 1) AS UNSIGNED) AS v1, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(v, \'.\', 2), \'.\', -1) AS UNSIGNED) AS v2, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(v, \'.\', 3), \'.\', -1) AS UNSIGNED) AS v3, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(v, \'.\', 4), \'.\', -1) AS UNSIGNED) AS v4 FROM ( SELECT \'6.3.5.5\' AS v UNION SELECT \'10.1.1.8\' UNION SELECT \'1.2.3.17\' UNION SELECT \'1.12.3.6\' UNION SELECT \'1.12.250.5\' UNION SELECT \'256.12.3.4\' ) AS t ORDER BY v1 DESC, v2 DESC, v3 DESC, v4 DESC;
效果:
若版本号只有3段的,则可以去掉上述 v4 部分。
另外,也可以不需要添加到查询的列里,直接放在 ORDER BY 子句中:
SELECT v FROM ( SELECT \'6.3.5.5\' AS v UNION SELECT \'10.1.1.1\' UNION SELECT \'1.2.3.4\' UNION SELECT \'1.12.3.4\' UNION SELECT \'1.12.250.4\' UNION SELECT \'256.12.3.4\' ) AS t ORDER BY CAST(SUBSTRING_INDEX(v, \'.\', 1) AS UNSIGNED) DESC, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(v, \'.\', 2), \'.\', -1) AS UNSIGNED) DESC, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(v, \'.\', 3), \'.\', -1) AS UNSIGNED) DESC, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(v, \'.\', 4), \'.\', -1) AS UNSIGNED) DESC;
效果:
另外,结合 INET_ATON(str) 函数可以把输入参数按IP地址格式排序,但是如果存在其中一段的数值超过255的记录,则排序对该记录不生效或结果不符合预期;若可以保证每一段均不超过255,则可以直接使用该函数辅助排序,省事。
来源:https://www.cnblogs.com/tods/p/16396714.html
本站部分图文来源于网络,如有侵权请联系删除。