gpt4 book ai didi

PHP MySQL - 对字母和数字字符串进行排序

转载 作者:行者123 更新时间:2023-11-29 10:10:37 27 4
gpt4 key购买 nike

我有以下数据:

GROUP 1 - FINANCE
GROUP 10 - SALES
GROUP 11 - MARKETING
GROUP 12 - HR
GROUP 2 - OPS

当MySQL中的数据ORDERING时,它会正确地按上述顺序返回该数据。但是,有没有办法(在不更改数据的情况下,您可以按 Numeric 顺序对其进行排序?如下

GROUP 1 - FINANCE
GROUP 2 - OPS
GROUP 10 - SALES
GROUP 11 - MARKETING
GROUP 12 - HR

谢谢!

最佳答案

添加 ORDER BY
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(column, ' ', 2), ' ', -1) AS UNSIGNED)
子句应该有所帮助。

使用正确的参数嵌套 SUBSTRING_INDEX 函数可以拆分和提取字符串部分。在这种情况下,将提取第一个字符串部分(在这种情况下为“GROUP”)之后的数字部分。

查询

SELECT
records.data
FROM (

SELECT
"GROUP 1 - FINANCE" AS data
UNION
SELECT
"GROUP 10 - SALES" AS data
UNION
SELECT
"GROUP 11 - MARKETING" AS data
UNION
SELECT
"GROUP 12 - HR" AS data
UNION
SELECT
"GROUP 2 - OPS" AS data
) AS records
ORDER BY
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(records.data, ' ', 2), ' ', -1) AS UNSIGNED)

查看演示 https://www.db-fiddle.com/f/sGqUeTKaDjihRA3Bg3Myca/0

关于PHP MySQL - 对字母和数字字符串进行排序,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51026062/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com