如何在MySQL的CONCAT中使用GROUP_CONCAT

Question

如果我在MySQL中有一个包含以下数据的表：

id       Name       Value
1          A          4
1          A          5
1          B          8
2          C          9

如何将它转换为以下格式？

id         Column
1          A:4,5,B:8
2          C:9

我觉得我需要使用GROUP_CONCAT。但是我不确定它是如何工作的。

- Biswa

Answer 1

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;

你可以在这里看到实现的例子：Sql Fiddle Demo。正是你所需要的。 通过分为两个步骤解释 首先，我们获得一个表，该表具有针对唯一[Name,id]的所有值（逗号分隔）。然后从获得的表中，我们将所有名称和值作为单个值针对每个唯一id获取。 在这里可以看到详细解释 SQL Fiddle Demo（向下滚动，因为它有两个结果集）。 编辑 在阅读问题时出现了错误，我只按id进行了分组。但是如果（值要按Name和id分组并且然后按id进行整体连接），则需要两个group_contacts。 之前的答案是。

select 
id,group_concat(concat(`name`,':',`value`) separator ',')
as Result from mytbl group by id

你可以在这里看到它的实现：SQL Fiddle Demo

- Sami

---

这并不是Biswa所要求的。 - eisberg

我认为警告人们只使用一种分隔符可能会带来不利影响很重要。我建议将“名称”分隔符设置为分号（;），而值的分隔符可以保持为逗号（,）。 - Fandi Susanto

请注意，GROUP_CONCAT 函数可能会将其输出悄悄截断到 group_concat_max_len。使用 SET group_concat_max_len=... 命令可以帮助解决此问题，但最好还是检查返回的（字节？）长度是否小于 group_concat_max_len。 - tuomassalo

供将来参考：https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat - kmonsoor

请注意，如果group_concat遇到单个NULL值，它将省略包含该值的整行。我在这里的第二个警告中解决了这个问题（https://stackoverflow.com/a/51369193/1054322）。 - MatrixManAtYrService

如果有人在答案中提供的 SQL Fiddle 链接上遇到问题，可以使用以下链接：http://sqlfiddle.com/#!9/42f994/601/0 - Hitesh

Answer 2

试试：

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

- eisberg

Answer 3

SELECT ID, GROUP_CONCAT(CONCAT_WS(':', NAME, VALUE) SEPARATOR ',') AS Result 
FROM test GROUP BY ID

- John

---

如果您能在回答中添加一些描述，那就太好了。这是为了改善现在和将来的回答而提出的建议。谢谢！ - Luís Cruz

Answer 4

首先，我不明白为什么会存在非唯一ID的原因，但我猜测这是一个连接到另一个表的ID。 其次，没有必要使用子查询，这会使服务器负担加重。您可以使用以下一次性查询来完成：

SELECT id,GROUP_CONCAT(name, ':', value SEPARATOR "|") FROM sample GROUP BY id

你可以快速准确地获得结果，并且可以使用分隔符“|”来拆分结果。我总是使用这个分隔符，因为它不可能在字符串中找到，因此它是唯一的。 有两个A没有问题，你只需要识别值即可。或者你可以多添加一列，带有字母，这样更好。 就像这样：

SELECT id,GROUP_CONCAT(DISTINCT(name)), GROUP_CONCAT(value SEPARATOR "|") FROM sample GROUP BY name

- Lucian Minea

Answer 5

 SELECT id, GROUP_CONCAT(CONCAT_WS(':', Name, CAST(Value AS CHAR(7))) SEPARATOR ',') AS result 
    FROM test GROUP BY id

你必须使用 cast 或 convert，否则将返回 BLOB。

结果是

id         Column
1          A:4,A:5,B:8
2          C:9

你需要再次通过编程语言，如Python或Java处理结果。

- lglcomcn

Answer 6

SELECT 
    id, 
    Group_concat(`column`) 
FROM
    (SELECT 
        id, 
        Concat(`name`, ':', Group_concat(`value`)) AS `column` 
    FROM mytbl 
    GROUP  BY id, name) tbl 
GROUP BY id; 

- Mrigank Shekhar

Answer 7

IF OBJECT_ID('master..test') 不为 null 则删除表 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

我的表名是test，我使用For XML Path('')语法进行连接。 STUFF函数将一个字符串插入到另一个字符串中。它会删除第一个字符串中指定长度的字符， 然后在第一个字符串的起始位置插入第二个字符串。

STUFF函数的格式如下：STUFF (character_expression, start, length, character_expression)

character_expression是字符数据的表达式。character_expression可以是常量、变量或者是字符或二进制数据的列。

start是一个整数值，用于指定删除和插入的位置。如果start或length为负数，则返回空字符串。如果start大于第一个character_expression，则返回空字符串。start可以是bigint类型。

length是一个整数，用于指定要删除的字符数。如果length比第一个character_expression长，则删除操作会一直进行到最后一个character_expression中的最后一个字符。length可以是bigint类型。

- Novy