我的数据库中有一个列，其中包含以下格式的数字1-1 1-2 2-1 2-2等我想按“第一个号码”、“第二个号码”订购我试过这个：
ORDER BY CAST('session_number' as signed) ASC
但这不是第二个数字的正确顺序。
63lcw9qa1#
试试这个：
select numsfrom ( select nums, position('-' in nums) `pos` from tbl) aorder by cast(substring(nums, 1, pos - 1) as signed), cast(substring(nums, pos + 1, length(nums) - pos) as signed)
select nums
from (
select nums, position('-' in nums) `pos` from tbl
) a
order by cast(substring(nums, 1, pos - 1) as signed),
cast(substring(nums, pos + 1, length(nums) - pos) as signed)
在 order by 第一句，你先按第一个数字订货（在破折号之前） - )，第二个顺序是根据第二个数字（在破折号之后）。
order by
-
8ulbf1ek2#
由第一方订购，取下子串并铸造至签字处：
CAST(SUBSTRING_INDEX(session_number, '-', 1) AS SIGNED) ASC, CAST(SUBSTRING_INDEX(session_number, '-', -1) 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;
CREATE VIEW v_sessions_ordered_by_session_number
AS
SELECT *
FROM sessions
ORDER BY
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 字段并保留十进制数据
session_number
1.1
1.2
2.1
session_number_order
2条答案
按热度按时间63lcw9qa1#
试试这个：
在
order by第一句，你先按第一个数字订货（在破折号之前）-)，第二个顺序是根据第二个数字（在破折号之后）。8ulbf1ek2#
由第一方订购，取下子串并铸造至签字处：
或创建已排序项目的视图：
然后将视图用作表：
但更好的解决方案是：
制造
session_number小数点如下：1.1,1.2,2.1等等如果不允许修改字段类型
创建额外的：
session_number_order字段并保留十进制数据