我的数据库中有一个列,其中包含格式为
1-1个
1-2个
2-1个
2-2个

我想按“第一个号码”、“第二个号码”订购
我试过这个:

ORDER BY CAST('session_number' as signed) ASC

但这并不是第二个数字的正确顺序。

最佳答案

通过将子字符串和强制类型转换为签名,按第一部分排序:

CAST(SUBSTRING_INDEX(session_number, '-', 1) AS SIGNED) ASC,
CAST(SUBSTRING_INDEX(session_number, '-', -1) AS SIGNED) ASC

或创建已排序项的视图:
CREATE VIEW v_sessions_ordered_by_session_number
AS
  SELECT *
  FROM sessions
  ORDER BY
    CAST(SUBSTRING_INDEX(session_number, '-', 1) AS SIGNED) ASC,
    CAST(SUBSTRING_INDEX(session_number, '-', -1) AS SIGNED) ASC;

然后将视图用作表:
SELECT some_field, session_number FROM v_sessions_ordered_by_session_number;

但更好的解决方案是:
session_number设为十进制,如:1.11.22.1
如果不允许修改字段类型
创建额外的:session_number_order字段并保留十进制数据

关于mysql - ORDER BY varchar列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/51260490/

10-13 00:50