在mysql中选择值的逗号分隔字符串

I want to convert selected values into a comma separated string in MySQL. My initial code is as follows:

我想在MySQL中将选定的值转换为逗号分隔的字符串。我的初始代码如下:

SELECT id FROM table_level where parent_id=4;

Which produced:

生产:

'5'
'6'
'9'
'10'
'12'
'14'
'15'
'17'
'18'
'779'

My desired output would look like this:

我的期望输出是这样的:

"5,6,9,10,12,14,15,17,18,779"

7 个解决方案

#1

103

Check this

检查这个

SELECT GROUP_CONCAT(id)  FROM table_level where parent_id=4 group by parent_id;

#2

If you have multiple rows for parent_id.

如果有多行parent_id。

SELECT GROUP_CONCAT(id) FROM table_level where parent_id=4 GROUP BY parent_id;

If you want to replace space with comma.

如果你想用逗号代替空格。

SELECT REPLACE(id,' ',',') FROM table_level where parent_id=4;

#3

Use group_concat() function of mysql.

使用group_concat()函数。

SELECT GROUP_CONCAT(id) FROM table_level where parent_id=4 GROUP BY parent_id;

It'll give you concatenated string like :

它会给你一个串联的字符串，比如:

5,6,9,10,12,14,15,17,18,779

#4

Try this

试试这个

SELECT CONCAT('"',GROUP_CONCAT(id),'"') FROM table_level 
where parent_id=4 group by parent_id;

Result will be

结果将是

 "5,6,9,10,12,14,15,17,18,779"

#5

Use group_concat method in mysql

在mysql中使用group_concat方法

#6

The default separator between values in a group is comma(,). To specify any other separator, use SEPARATOR as shown below.

组中的值之间的默认分隔符是逗号(，)。要指定任何其他分隔符，请使用分隔符，如下所示。

SELECT GROUP_CONCAT(id SEPARATOR '|')
FROM `table_level`
WHERE `parent_id`=4
GROUP BY `parent_id`;

5|6|9|10|12|14|15|17|18|779

5 | 6 | 9 | 10 12 | | | 17 18 | 779 | | 15岁了

To eliminate the separator, then use SEPARATOR ''

要消除分隔符，请使用分隔符"

SELECT GROUP_CONCAT(id SEPARATOR '')
FROM `table_level`
WHERE `parent_id`=4
GROUP BY `parent_id`;

Refer for more info GROUP_CONCAT

请参阅更多GROUP_CONCAT信息

#7

First to set group_concat_max_len, otherwise it will not give you all the result:

首先设置group_concat_max_len，否则不会得到所有结果:

SET GLOBAL  group_concat_max_len = 999999;
SELECT GROUP_CONCAT(id)  FROM table_level where parent_id=4 group by parent_id;

#1

103