背景:
因项目需要,需对字符串型数字进行排序
方法一:
-
原理:将长度一致的字符串先排序,再按照字符串的ASCII码排序。
-
SQL语句:
SELECT transactionGmv6Months FROM company WHERE categoryId=127726077 ORDER BY LENGTH(transactionGmv6Months),transactionGmv6Months;
-
查询结果:
方法二:
- 原理:将字符串中不需要的字符先去掉,再转换为数字类型进行排序【推荐】。
- SQL语句:
SELECT transactionGmv6Months FROM company WHERE categoryId=127726077 ORDER BY CONVERT(REPLACE(REPLACE(transactionGmv6Months,'+',''),',',''),SIGNED) DESC
- 查询结果: