将多行合并为一行MySQL [英] Combine multiple rows into one row MySQL

问题描述

说我在MySQL数据库中有两个表.

Say I have two tables in a MySQL Database.

表1:

ID    Name
1     Jim
2     Bob
3     John

表2:

ID    key           value
1     address       "X Street"
1     city          "NY"
1     region        "NY"
1     country       "USA"
1     postal_code   ""
1     phone         "123456789"

从数据库中选择行时，是否有任何方法可以将第二个表中的行作为列连接到第一个表中?

When selecting rows from the database, is there any way to join rows from the second table as columns to the first table?

直接从MySQL查询获得的期望结果是:

The desired result right from the MySQL query is:

ID    Name    address    city    region   country   postal_code   phone
1     Jim     X Street   NY      NY       USA       NULL          123456789
2     Bob     NULL       NULL    NULL     NULL      NULL          NULL
3     John    NULL       NULL    NULL     NULL      NULL          NULL

感谢您的帮助！

推荐答案

这种数据转换类型称为PIVOT. MySQL没有枢轴函数，但是您可以使用带有CASE表达式的聚合函数来复制它:

This type of data transformation is known as a PIVOT. MySQL doesn't have a pivot function but you can replicate it using an aggregate function with a CASE expression:

select t1.id,
  t1.name,
  max(case when t2.`key` = 'address' then t2.value end) address,
  max(case when t2.`key` = 'city' then t2.value end) city,
  max(case when t2.`key` = 'region' then t2.value end) region,
  max(case when t2.`key` = 'country' then t2.value end) country,
  max(case when t2.`key` = 'postal_code' then t2.value end) postal_code,
  max(case when t2.`key` = 'phone' then t2.value end) phone
from table1 t1
left join table2 t2
  on t1.id = t2.id
group by t1.id, t1.name

请参见带演示的SQL提琴.

这也可以在您的table2上使用多个联接来编写，并且您将在每个key的联接上包括一个过滤器:

This could also be written using multiple joins on your table2 and you would include a filter on the join for each key:

select t1.id,
  t1.name,
  t2a.value address,
  t2c.value city,
  t2r.value region,
  t2y.value country,
  t2pc.value postal_code,
  t2p.value phone
from table1 t1
left join table2 t2a
  on t1.id = t2a.id
  and t2a.`key` = 'address'
left join table2 t2c
  on t1.id = t2c.id
  and t2c.`key` = 'city' 
left join table2 t2r
  on t1.id = t2r.id
  and t2c.`key` = 'region' 
left join table2 t2y
  on t1.id = t2y.id
  and t2c.`key` = 'country' 
left join table2 t2pc
  on t1.id = t2pc.id
  and t2pc.`key` = 'postal_code' 
left join table2 t2p
  on t1.id = t2p.id
  and t2p.`key` = 'phone';

请参见带演示的SQL提琴.

如果您的key值数量有限，则上述两个版本将非常有用.如果您有未知数量的值，那么您将需要使用准备好的语句来生成动态SQL:

The above two versions will work great if you have a limited number of key values. If you have an unknown number of values, then you will want to look at using a prepared statement to generate dynamic SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when t2.`key` = ''',
      `key`,
      ''' then t2.value end) AS `',
      `key`, '`'
    )
  ) INTO @sql
from Table2;

SET @sql 
    = CONCAT('SELECT t1.id, t1.name, ', @sql, ' 
              from table1 t1
              left join table2 t2
                on t1.id = t2.id
              group by t1.id, t1.name;');

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

请参见带有演示的SQL提琴

所有版本都会给出结果:

All versions will give a result:

| ID | NAME |  ADDRESS |   CITY | REGION | COUNTRY | POSTAL_CODE |     PHONE |
|----|------|----------|--------|--------|---------|-------------|-----------|
|  1 |  Jim | X Street |     NY | (null) |  (null) |      (null) | 123456789 |
|  2 |  Bob |   (null) | (null) | (null) |  (null) |      (null) |    (null) |
|  3 | John |   (null) | (null) | (null) |  (null) |      (null) |    (null) |

这篇关于将多行合并为一行MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！