MySQL-在列中对逗号分隔的字符串进行排序 [英] MySQL - sort comma separated string in column

问题描述

我有桌子:

+-----+------------+--------------+
| id  | title      | numbers      |
| 2   | Title 1    | 2,8,5        |
| 3   | Title 2    | 50,7,9,4     |
+-----+------------+--------------+

是否可以在列中进行排序?在这种情况下，请输入列号.

Is it possible to sort within the column? In this case in column numbers.

我需要输出订购号列，如下所示:

I need to output ordered numbers column as follows:

+-----+------------+--------------+
| id  | title      | numbers      |
| 2   | Title 1    | 2,5,8        |
| 3   | Title 2    | 4,7,9,50     |
+-----+------------+--------------+

类似的东西:

SELECT id, title, SORT_FUNC(numbers) from table

我一直在寻找MySQL文档中的某些功能，但一无所获.

I was looking for some function in MySQL documentation, but I found nothing.

推荐答案

可能，但并不是一个好主意.

It is possible, but not really a good idea.

作为示例，您可以通过生成一个数字范围并将其与SUBSTRING_INDEX结合使用来拆分逗号分隔的列表，以获取每个元素.但是，数字范围必须与定界值的最大数目一样大.

As an example, you can split a comma separated list up by generating a range of numbers and using that with SUBSTRING_INDEX to get each element. However the range of numbers needs to be as big as the max number of delimited values.

然后，您可以使用GROUP_CONCAT以正确的顺序将列表重新加入在一起.请注意，顺序将有所不同，具体取决于您是将拆分值转换为数字/整数还是将其保留为字符串.

You could then use GROUP_CONCAT to join the list back together in the right order. Note that the order will be different depending on whether you have cast the split up values as numbers / integers or left them as strings.

SELECT id, title, GROUP_CONCAT(aNumber ORDER BY aNumber)
FROM
(
    SELECT id, title, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(numbers, ',', tens.acnt * 10 + units.acnt + 1), ',', -1) AS UNSIGNED) AS aNumber
    FROM some_table
    CROSS JOIN
    (SELECT 0 AS acnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN
    (SELECT 0 AS acnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
    WHERE LENGTH(numbers) - LENGTH(REPLACE(numbers, ',', '')) >= tens.acnt * 10 + units.acnt
) sub0
GROUP BY id, title;

此处在SQL小提琴上进行了演示(如果SQL小提琴决定工作的话):-

Demonstrated here on SQL fiddle (if SQL fiddle decides to work):-

http://www.sqlfiddle.com/#!9/c9703ee/4

第一个选择是将值转换为整数以数字方式对其进行排序，第二个选择不是将其转换为字符串而是仅将其保留为字符串，因此排序顺序是不同的.

First select is casting the values as integers to sort them numerically, 2nd one isn't casting them but just leaving them as strings, hence the sort order is different.

这篇关于MySQL-在列中对逗号分隔的字符串进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！