问题 MySQL按字母顺序排列

我有字符串如 M1 M3 M4 M14 M30 M40 等（字母后面的任何int 2-3位数）当我按“ORDER BY name”返回时：

M1, M14, M3, M30, M4, M40

当我想要：

M1, M3, M4, M14, M30, M40 它将整个事物视为字符串，但我想将其视为字符串+ int

有任何想法吗？

4268

2017-08-23 17:50

起源

stackoverflow.com/a/153642/1013082 - MetalFrog

字符串的开头是否只有一个字母？ - Zane Bien

stackoverflow.com/a/12257917/2008111 - caramba

答案:

您可以在ORDER BY中使用SUBSTR和CAST AS UNSIGNED / SIGNED：

SELECT * FROM table_name ORDER BY
    SUBSTR(col_name FROM 1 FOR 1),
    CAST(SUBSTR(col_name FROM 2) AS UNSIGNED)

2017-08-23 18:04

非常感谢！刚从1 FOR 2改为FROM 1 FOR 1并且正常工作 - Adam Esterle

真棒的回答......你结识了我的一天！谢谢！ - Monica

@rocky如果没有固定合成器怎么办，可能的字符串喜欢：test1,1test，te2st，test11,2131,10t，t22g - Er.KT

如果字符串的开头可以有多个字符，例如like 'M10', 'MTR10', 'ABCD50', 'JL8', etc...，你基本上必须从数字的第一个位置获取名称的子字符串。

不幸的是，MySQL不支持那种REGEXP操作（只返回一个布尔值，而不是实际匹配）。

您可以使用此解决方案来模拟它：

SELECT   name
FROM     tbl
ORDER BY CASE WHEN ASCII(SUBSTRING(name,1)) BETWEEN 48 AND 57 THEN
                   CAST(name AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,2)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,1)
              WHEN ASCII(SUBSTRING(name,3)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,2)
              WHEN ASCII(SUBSTRING(name,4)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,3)
              WHEN ASCII(SUBSTRING(name,5)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,4)
              WHEN ASCII(SUBSTRING(name,6)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,5)
              WHEN ASCII(SUBSTRING(name,7)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,6)
              WHEN ASCII(SUBSTRING(name,8)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,7)
         END,
         CASE WHEN ASCII(SUBSTRING(name,1)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,1) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,2)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,2) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,3)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,3) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,4)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,4) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,5)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,5) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,6)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,6) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,7)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,7) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,8)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,8) AS UNSIGNED)
         END

这将首先按字符串的字符部分排序，然后是字符串的提取数字部分，只要在字符串的开头有<= 7个字符。如果您需要更多，您可以直接链接 WHEN到了 CASE 声明。

2017-08-23 18:29

为我工作。谢谢：） - akash varlani

如果它的数字值如下：“M10”，“40”，“MTR10”，“ABCD50”，“8”，“JL8”，“55” - sytolk

它需要在第一个CASE之前添加ORDER BY name * 1，CASE ..如果它在带有数字的混合字符串之间有干净的数字。 - sytolk

我无法解决我的问题，即如下所示排序MLS数字：

V12345 V1000000 V92832

问题是V1000000的价值并不比其他人高，即使它更大。

用这解决了我的问题：

ORDER BY CAST(SUBSTR(col_name FROM 2) AS UNSIGNED) DESC

刚刚删除了 SUBSTR(col_name FROM 1 FOR 1)

2017-07-16 18:21

您可以使用：

order by name,SUBSTRING(name,1,LENGTH(name)-1)

2017-08-23 18:07

对我来说，这是正确的解决方案。谢谢！ - Stimart

它将数字和字母分开。

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(col,'1', 1), '2', 1), '3', 1), '4', 1), '5', 1), '6', 1)
, '7', 1), '8', 1), '9', 1), '0', 1) as new_col  
FROM table group by new_col;

2018-04-08 12:35

尝试使用SUBSTR删除该字符。然后使用ABS从字段中获取绝对值：

SELECT * FROM table ORDER BY ABS(SUBSTR(field,1));

2017-11-08 13:19

问题 MySQL按字母顺序排列

答案:

热门问题