如何在MySQL的CONCAT中使用GROUP_CONCAT [英] How to use GROUP_CONCAT in a CONCAT in MySQL
问题描述
如果我在MySQL中有一个包含以下数据的表:
If I have a table with the following data in 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
我想我必须使用GROUP_CONCAT
.但是我不确定它是如何工作的.
I think I have to use GROUP_CONCAT
. But I'm not sure how it works.
推荐答案
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演示 .正是您所需要的.
You can see it implemented here : Sql Fiddle Demo. Exactly what you need.
更新 分两步进行.首先,我们得到一个表,该表具有针对唯一[Name,id]的所有值(用逗号分隔).然后从获得的表中,将所有名称和值作为针对每个唯一ID的单个值 参见 SQL Fiddle演示 (在此向下滚动)有两个结果集)
Update Splitting 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)
编辑:阅读问题时出现错误,我仅按ID分组.但是,如果需要两个group_contacts(值将按名称和ID分组,然后按ID整体分组). 上一个答案是
Edit There 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
select
id,group_concat(concat(`name`,':',`value`) separator ',')
as Result from mytbl group by id
您可以在此处看到它的实现: SQL小提琴演示
You can see it implemented here : SQL Fiddle Demo
这篇关于如何在MySQL的CONCAT中使用GROUP_CONCAT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!