如何在 MySQL 的 CONCAT 中使用 GROUP_CONCAT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13451605/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use GROUP_CONCAT in a CONCAT in MySQL
提问by Biswa
If I have a table with the following data in MySQL:
如果我在 MySQL 中有一个包含以下数据的表：
id Name Value
1 A 4
1 A 5
1 B 8
2 C 9
how do I get it into the following format?
我如何将其转换为以下格式？
id Column
1 A:4,5,B:8
2 C:9
I think I have to use GROUP_CONCAT. But I'm not sure how it works.
我想我必须使用GROUP_CONCAT. 但我不确定它是如何工作的。
回答by Sami
select id, group_concat(`Name` separator ',') as `ColumnName`
from
(
select id, concat(`Name`, ':',
group_concat(`Value` separator ',')) as `Name`
from mytbl
group by id, `Name`
) tbl
group by id;
You can see it implemented here : Sql Fiddle Demo. Exactly what you need.
你可以在这里看到它的实现：Sql Fiddle Demo。正是您所需要的。
UpdateSplitting in two steps. First we get a table having all values(comma separated) against a unique[Name,id]. Then from obtained table we get all names and values as a single value against each unique id See this explained here SQL Fiddle Demo(scroll down as it has two result sets)
分两步更新拆分。首先，我们得到一个表，其中包含针对唯一 [Name,id] 的所有值（逗号分隔）。然后从获得的表中，我们将所有名称和值作为针对每个唯一 id 的单个值，请参阅此处解释的SQL Fiddle Demo（向下滚动，因为它有两个结果集）
EditThere was a mistake in reading question, I had grouped only by id. But two group_contacts are needed if (Values are to be concatenated grouped by Name and id and then over all by id). Previous answer was
编辑阅读问题时出错，我仅按 id 分组。但是如果（值要按名称和 id 分组，然后按 id 分组），则需要两个 group_contacts。之前的回答是
select
id,group_concat(concat(`name`,':',`value`) separator ',')
as Result from mytbl group by id
You can see it implemented here : SQL Fiddle Demo
你可以在这里看到它的实现：SQL Fiddle Demo
回答by eisberg
Try:
尝试：
CREATE TABLE test (
ID INTEGER,
NAME VARCHAR (50),
VALUE INTEGER
);
INSERT INTO test VALUES (1, 'A', 4);
INSERT INTO test VALUES (1, 'A', 5);
INSERT INTO test VALUES (1, 'B', 8);
INSERT INTO test VALUES (2, 'C', 9);
SELECT ID, GROUP_CONCAT(NAME ORDER BY NAME ASC SEPARATOR ',')
FROM (
SELECT ID, CONCAT(NAME, ':', GROUP_CONCAT(VALUE ORDER BY VALUE ASC SEPARATOR ',')) AS NAME
FROM test
GROUP BY ID, NAME
) AS A
GROUP BY ID;
SQL Fiddle: http://sqlfiddle.com/#!2/b5abe/9/0
SQL 小提琴：http://sqlfiddle.com/#!2/b5abe/9/0
回答by John
SELECT ID, GROUP_CONCAT(CONCAT_WS(':', NAME, VALUE) SEPARATOR ',') AS Result
FROM test GROUP BY ID
回答by Lucian Minea
First of all, I don't see the reason for having an ID that's not unique, but I guess it's an ID that connects to another table. Second there is no need for subqueries, which beats up the server. You do this in one query, like this
首先，我看不出有一个不唯一的 ID 的原因，但我猜它是一个连接到另一个表的 ID。其次，不需要子查询，这会打败服务器。您在一个查询中执行此操作，如下所示
SELECT id,GROUP_CONCAT(name, ':', value SEPARATOR "|") FROM sample GROUP BY id
You get fast and correct results, and you can split the result by that SEPARATOR "|". I always use this separator, because it's impossible to find it inside a string, therefor it's unique. There is no problem having two A's, you identify only the value. Or you can have one more colum, with the letter, which is even better. Like this :
你得到快速和正确的结果，你可以用分隔符“|”分割结果。我总是使用这个分隔符，因为不可能在字符串中找到它，因此它是唯一的。有两个 A 没有问题，您只需确定值。或者，您可以多列一个带有字母的列，这样更好。像这样 ：
SELECT id,GROUP_CONCAT(DISTINCT(name)), GROUP_CONCAT(value SEPARATOR "|") FROM sample GROUP BY name
回答by lglcomcn
SELECT id, GROUP_CONCAT(CONCAT_WS(':', Name, CAST(Value AS CHAR(7))) SEPARATOR ',') AS result
FROM test GROUP BY id
you must use cast or convert, otherwise will be return BLOB
您必须使用强制转换或转换，否则将返回 BLOB
result is
结果是
id Column
1 A:4,A:5,B:8
2 C:9
you have to handle result once again by program such as python or java
您必须通过python或java等程序再次处理结果
回答by Mrigank Shekhar
SELECT id,
Group_concat(column)
FROM (SELECT id,
Concat(name, ':', Group_concat(value)) AS columnFROM mytbl
GROUP BY id,
name) tbl
GROUP BY id;
SELECT id, Group_concat( column) FROM (SELECT id, Concat( name, ':', Group_concat( value)) AS columnFROM mytbl GROUP BY id, name) tbl GROUP BY id;
回答by Novy
IF OBJECT_ID('master..test') is not null Drop table test
IF OBJECT_ID('master..test') is not null Drop table test
CREATE TABLE test (ID INTEGER, NAME VARCHAR (50), VALUE INTEGER );
INSERT INTO test VALUES (1, 'A', 4);
INSERT INTO test VALUES (1, 'A', 5);
INSERT INTO test VALUES (1, 'B', 8);
INSERT INTO test VALUES (2, 'C', 9);
select distinct NAME , LIST = Replace(Replace(Stuff((select ',', +Value from test where name = _a.name for xml path('')), 1,1,''),'<Value>', ''),'</Value>','') from test _a order by 1 desc
My table name is test , and for concatination I use the For XML Path('') syntax. The stuff function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
我的表名是 test ，对于连接，我使用 For XML Path('') 语法。stuff 函数将一个字符串插入到另一个字符串中。它在起始位置删除第一个字符串中指定长度的字符，然后将第二个字符串插入到起始位置的第一个字符串中。
STUFF functions looks like this : STUFF (character_expression , start , length ,character_expression )
STUFF 函数如下所示： STUFF (character_expression , start , length ,character_expression )
character_expression Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.
character_expression 是字符数据的表达式。character_expression 可以是常量、变量或字符或二进制数据的列。
start Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.
start 是一个整数值，指定开始删除和插入的位置。如果 start 或 length 为负，则返回空字符串。如果 start 比第一个 character_expression 长，则返回空字符串。start 可以是 bigint 类型。
length Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression. length can be of type bigint.
length 是一个整数，指定要删除的字符数。如果长度比第一个 character_expression 长，则删除直到最后一个 character_expression 中的最后一个字符。length 可以是 bigint 类型。
