将多行合并为一行 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21118809/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Combine multiple rows into one row MySQL
提问by n0nnus
Say I have two tables in a MySQL Database.
假设我在 MySQL 数据库中有两个表。
Table 1:
表格1：
ID Name
1 Jim
2 Bob
3 John
Table 2:
表 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?
从数据库中选择行时，有没有办法将第二个表中的行作为列连接到第一个表？
The desired result right from the MySQL query is:
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
Thanks for any help!
谢谢你的帮助！
回答by Taryn
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 CASEexpression:
这种类型的数据转换称为 PIVOT。MySQL 没有枢轴函数，但您可以使用带有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
See SQL Fiddle with Demo.
This could also be written using multiple joins on your table2and you would include a filter on the join for each key:
这也可以使用您的多个连接编写，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';
See SQL Fiddle with Demo.
The above two versions will work great if you have a limited number of keyvalues. If you have an unknown number of values, then you will want to look at using a prepared statement to generate dynamic SQL:
如果您的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;
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) |
回答by Gordon Linoff
You have a structure called entity-attribute-value in the second table. There are two ways to do the combination. I think the aggregation method is the easier to express:
在第二个表中有一个名为 entity-attribute-value 的结构。有两种方法可以进行组合。我认为聚合方法更容易表达：
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;
The second method is to do separate joins for each value:
第二种方法是对每个值进行单独的连接：
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' . . .
Depending on the structure of the data, the joinmethod can actually be faster in MySQL when it uses appropriate indexing. (Other databases have been algorithms for aggregation, so the group bymethod often works well in other databases.)
根据数据的结构，join当使用适当的索引时，该方法在 MySQL 中实际上可以更快。（其他数据库一直是聚合算法，所以该group by方法在其他数据库中通常效果很好。）
