SQL 顺序字符串作为数字

我有数字保存为 VARCHAR到一个 MySQL 数据库。我不能使他们 INT由于其他一些取决于情况。

它在排序时把它们当作字符而不是数字。

数据库里有

1 2 3 4 5 6 7 8 9 10...

在我的页面上显示的有序列表如下:

1 10 2 3 4 5 6 7 8 9

我怎样才能使它看起来按数字升序排列？

270765 次浏览
小开
最佳答案

如果可能的话，如果只存储数字，应该将列的数据类型更改为数字。

如果不能这样做，那么将列值强制转换为 integer 明确地

select col from yourtable
order by cast(col as unsigned)

或者 毫无疑问，例如使用数学运算强制转换为数字

select col from yourtable
order by col + 0

MySQL 将字符串从左到右转换。示例:

string value  |  integer value after conversion
--------------+--------------------------------
'1'           |  1
'ABC'         |  0   /* the string does not contain a number, so the result is 0 */
'123miles'    |  123
'$123'        |  0   /* the left side of the string does not start with a number */
小开

改变信仰的另一种方式。

如果您有字符串字段，您可以按照以下方式转换它或其数值部分: 添加前导零使所有整数字符串具有相同的长度。

ORDER BY CONCAT( REPEAT(  "0", 18 - LENGTH( stringfield ) ) , stringfield )

或者按字段的一部分排序，比如“ tensymbols13”、“ tensymbols1222”等等。

ORDER BY CONCAT( REPEAT(  "0", 18 - LENGTH( LEFT( stringfield , 10 ) ) ) , LEFT( stringfield , 10 ) )
小开

另一种方法，不使用一个单一的石膏。

(对于使用 JPA 2.0的用户，不允许进行强制转换)

select col from yourtable
order by length(col),col

编辑: 只适用于正整数

小开

我正在排序的专栏有任何 alpha 和数字的组合，所以我用这篇文章中的建议作为一个起点，并得出了这个结论。

DECLARE @tmp TABLE (ID VARCHAR(50));
INSERT INTO @tmp VALUES ('XYZ300');
INSERT INTO @tmp VALUES ('XYZ1002');
INSERT INTO @tmp VALUES ('106');
INSERT INTO @tmp VALUES ('206');
INSERT INTO @tmp VALUES ('1002');
INSERT INTO @tmp VALUES ('J206');
INSERT INTO @tmp VALUES ('J1002');


SELECT ID, (CASE WHEN ISNUMERIC(ID) = 1 THEN 0 ELSE 1 END) IsNum
FROM @tmp
ORDER BY IsNum, LEN(ID), ID;

结果

ID
------------------------
106
206
1002
J206
J1002
XYZ300
XYZ1002

希望这个能帮上忙

小开

将字段更改为 INT 而不是 VARCHAR。

我不能使他们 INT 由于其他一些取决于情况。

那就先解决相关问题。否则，你就是在解决真正的根本问题。使用 MySQLCAST 是一种选择，但它掩盖了应该修复的错误模式。

小开

我还在查看一个有字母前缀的排序字段。这是我找到的解决办法。这可能有助于寻找相同解决方案的人。

字段值:

FL01,FL02,FL03,FL04,FL05,...FL100,...FL123456789


select SUBSTRING(field,3,9) as field from table order by SUBSTRING(field,3,10)*1 desc

我放9是因为9对我来说足够保持最大9位数的整数值。

结果就是 123456789 123456788 123456787 ... 100 99 ... 2 1

小开

另一个简单的方法

ORDER BY ABS(column_name)

小开

这将处理负数，分数，字符串，一切:

ORDER BY ISNUMERIC(col) DESC, Try_Parse(col AS decimal(10,2)), col;
小开

这对我有用。

select * from tablename
order by cast(columnname as int) asc
小开

这可能有助于寻找相同解决方案的人。

select * from tablename ORDER BY ABS(column_name)
小开

如果您正在使用 AdonisJS 并且具有混合 ID，比如 ABC-202、 ABC-201... ，那么您可以将原始查询与 Query Builder 组合起来，并按照以下方式实现上面的解决方案(https://stackoverflow.com/a/25061144/4040835) :

const sortField =
'membership_id'
const sortDirection =
'asc'
const subquery = UserProfile.query()
.select(
'user_profiles.id',
'user_profiles.user_id',
'user_profiles.membership_id',
'user_profiles.first_name',
'user_profiles.middle_name',
'user_profiles.last_name',
'user_profiles.mobile_number',
'countries.citizenship',
'states.name as state_of_origin',
'user_profiles.gender',
'user_profiles.created_at',
'user_profiles.updated_at'
)
.leftJoin(
'users',
'user_profiles.user_id',
'users.id'
)
.leftJoin(
'countries',
'user_profiles.nationality',
'countries.id'
)
.leftJoin(
'states',
'user_profiles.state_of_origin',
'states.id'
)
.orderByRaw(
`SUBSTRING(:sortField:,3,15)*1 ${sortDirection}`,
{
sortField: sortField,
}
)
.paginate(
page,
per_page
)

备注: 在这一行: SUBSTRING(:sortField:,3,15)*1 ${sortDirection},

  1. “3” 表示数字前最后一个非数字字符的索引号。如果您的混合 ID 是“ ABC-123”，那么您的索引号将是4。
  2. “15” 用于在连字符后捕获尽可能多的数字。
  3. ’1’ 对子字符串执行数学运算，有效地将子字符串强制转换为数字。

参考文献1: 你可以在这里阅读更多关于原始查询中的参数绑定: < a href = “ https://knexjs.org/# Raw-Bindings”rel = “ nofollow norefrer”> https://knexjs.org/#raw-bindings 参考文献2: 阿多尼斯原始查询: < a href = “ https://adonisjs.com/docs/4.1/query-building # _ Raw _ Query”rel = “ nofollow noReferrer”> https://adonisjs.com/docs/4.1/query-builder#_raw_queries


提交答案