将多行合并为一行 MySQL

Question

将多行合并为一行 MySQL

13

假设我在一个MySQL数据库中有两个表。

第一个表：

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"

在从数据库选择行时，有没有办法将第二个表中的行作为列连接到第一个表中？

MySQL查询的期望结果是：

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

感谢任何帮助！

- n0nnus

2个回答

3

第二个表中有一个名为实体-属性-值的结构。这种组合可以通过两种方式实现。我认为聚合方法更容易表达：

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

第二种方法是为每个值单独执行连接操作：

select t1.name, address.value, city.value, . . .
from table1 t1 left join
     table2 address
     on t1.id = address.id and address.`key` = 'Address' left join
     table2 city
     on t1.id = city.id and city.`key` = 'City' . . .

根据数据的结构，join方法在MySQL中使用适当的索引实际上可以更快。（其他数据库有聚合算法，因此group by方法在其他数据库中通常运作良好。）

- Gordon Linoff

我很好奇为什么这里的方法＃1（使用case语句）似乎在这种情况下不起作用？（http://stackoverflow.com/questions/23377374/using-case-to-convert-column-values-to-row-values-in-mysql） - JackhammersForWeeks

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Taryn · Accepted Answer

这种数据转换被称为“PIVOT”。MySQL没有Pivot函数，但是您可以使用聚合函数和CASE表达式来复制它：

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 Fiddle。

你也可以使用在table2上进行多个连接的方式来编写它，并且你需要在每个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 Fiddle。

如果您只有有限数量的key值，那么上面两个版本将非常有效。如果你有未知数量的值，那么你需要使用准备语句来生成动态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 Fiddle

所有版本都将给出结果:

| 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) |