MYSQL选择一个字符串并按该顺序排序 [英] MYSQL select a piece of a string and order by that piece

问题描述

我有一个带有此类信息的字段"web-1/1.，web-2/2.，web-3/3.，web-4/4.，web-5/5.".其他寄存器可能具有不同的值，例如"web-1/4.，web-2/5.，web-3/1.，web-4/2.，web-5/3".

I have a field with this kind of info "web-1/1.,web-2/2.,web-3/3.,web-4/4.,web-5/5.". Other registers could have different values like "web-1/4.,web-2/5.,web-3/1.,web-4/2.,web-5/3."

我想通过web-2/进行选择和订购吗?将是web-2/1，web-2/2，web-2/3，以此类推，所有包含web-2并按最后一个数字排序的字段

I want to select and order by lets say web-2/? would be web-2/1, web-2/2, web-2/3 and so on all fields that contain web-2 and order by the last number

我想在其他网站上创建功能特色脚本并指定功能编号.不同的属性，不同的网站顺序不同

I want to create a featured properties script different websites and specify feature number. Different properties, different websites different order

推荐答案

我建议您看看

I would suggest that you look at the MySQL String Functions and more specifically the SUBSTRING_INDEX function. The reason I suggest this one over SUBSTRING is because the number before or after the slash might be more than a single number which would make the length of the first and/or second parts vary.

示例:

SELECT   `info`,
         SUBSTRING_INDEX(`info`, '/', 1) AS `first_part`,
         SUBSTRING_INDEX(`info`, '/', -1) AS `second_part`
FROM     `table`
ORDER BY `first_part` ASC,
         `second_part` ASC;

结果:

其他示例

在此示例中，我使用的是 ，以将第二部分转换为无符号整数，以防第二部分包含符号或字母等其他字符.换句话说，"web-4/15"的第二部分.将是"15"，"web-4/15 ****"的第二部分也将是"15".

In this example, I'm using CAST to convert the second part into an unsigned integer just in case it contains additional characters such as symbols or letters. In other words, the second part of "web-4/15." would be "15" and the second part of "web-4/15****" would also be "15".

SELECT   `info`,
          SUBSTRING_INDEX(`info`, '/', 1) AS `first_part`,
          CAST(SUBSTRING_INDEX(`info`, '/', -1) AS UNSIGNED) `second_part`
FROM     `table`
ORDER BY `first_part` ASC,
         `second_part` ASC;

这篇关于MYSQL选择一个字符串并按该顺序排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！