我的数据库中有一个列,其中包含格式为
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.1
,1.2
,2.1
等如果不允许修改字段类型
创建额外的:
session_number_order
字段并保留十进制数据关于mysql - ORDER BY varchar列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/51260490/