問題描述

如果我在 MySQL 中有一個(gè)包含以下數(shù)據(jù)的表:

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

如何將其轉(zhuǎn)換為以下格式?

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;

你可以在這里看到它的實(shí)現(xiàn):Sql Fiddle Demo.正是您所需要的.

You can see it implemented here : Sql Fiddle Demo. Exactly what you need.

更新分兩步拆分.首先，我們得到一個(gè)表，其中包含針對唯一 [Name,id] 的所有值(逗號分隔).然后從獲得的表中，我們將所有名稱和值作為針對每個(gè)唯一 id 的單個(gè)值請參閱此處的說明 SQL Fiddle Demo(向下滾動(dòng)有兩個(gè)結(jié)果集)

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)

編輯 閱讀問題時(shí)出錯(cuò)，我僅按 id 分組.但是如果(值要按名稱和 id 分組，然后按 id 分組)，則需要兩個(gè) group_contacts.以前的答案是

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

你可以在這里看到它的實(shí)現(xiàn):SQL Fiddle Demo

You can see it implemented here : SQL Fiddle Demo

這篇關(guān)于如何在 MySQL 的 CONCAT 中使用 GROUP_CONCAT的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！