MySQL-在列中进行一条记录 [英] MySQL - Make one record out of a column

问题描述

我无法弄清楚这一点.

我有这张桌子:

id    component_id     data
1     1                [e-mail]
1     2                firstname1
1     3                lastname1
1     4                phone1
2     1                email2
2     2                firstname2
2     3                lastname2
2     4                phone2

现在，我想查询一个将根据ID提取所有数据的查询.我想要这些记录:

Now I want to have a query that will extract all the data according to the id. I want to have these records:

1  [e-mail]  firstname1  lastname1  phone1
2  email2  firstname2  lastname2  phone2

我希望我能理解....

I hope I'm understood....

推荐答案

您要的基本上是PIVOT，但是MySQL没有数据透视功能，因此您可以使用CASE语句将其复制功能.

What you are asking for is basically a PIVOT but MySQL does not have a pivot function so you can replicate this using a CASE statement with an aggregate function.

如果您知道这些值，则可以对解决方案进行硬编码，类似于以下内容:

If you know the values, then you can hard-code the solution similar to this:

select id,
  max(case when component_id = 1 then data end) Email,
  max(case when component_id = 2 then data end) Firstname,
  max(case when component_id = 3 then data end) Lastname,
  max(case when component_id = 4 then data end) Phone
from yourtable
group by id;

请参见带有演示的SQL提琴

结果是:

| ID |            EMAIL |  FIRSTNAME |  LASTNAME |  PHONE |
-----------------------------------------------------------
|  1 | [e-mail] | firstname1 | lastname1 | phone1 |
|  2 |           email2 | firstname2 | lastname2 | phone2 |

我猜您有一个表将component_id与名称相关联，因此您的查询也可能是:

I am guessing that you have a table to associate the component_id with a name so your query could also be:

select t1.id,
  max(case when t2.name = 'email' then data end) Email,
  max(case when t2.name= 'FirstName' then data end) Firstname,
  max(case when t2.name= 'LastName' then data end) Lastname,
  max(case when t2.name= 'phone' then data end) Phone
from yourtable t1
inner join component t2
  on t1.component_id = t2.id 
group by t1.id;

请参见带有演示的SQL提琴

如果值的数量未知，则可以使用准备好的语句动态生成此查询:

If you have an unknown number of values, then you can use a prepared statement to generate this query dynamically:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when name = ''',
      name,
      ''' then data end) AS ',
      name
    )
  ) INTO @sql
FROM component;

SET @sql = CONCAT('SELECT t1.id, ', @sql, ' 
                  from yourtable t1
                  inner join component t2
                    on t1.component_id = t2.id 
                  group by t1.id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

请参见带有演示的SQL提琴

所有版本都会为您带来相同的结果.

All versions will give you the same result.

这篇关于MySQL-在列中进行一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！