将多行合并为一行MySQL

将多行合并为一行MySQL

这种数据转换类型称为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_pre' then t2.value end) postal_pre,  max(case when t2.`key` = 'phone' then t2.value end) phonefrom table1 t1left join table2 t2  on t1.id = t2.idgroup 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_pre,  t2p.value phonefrom table1 t1left 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_pre' 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 @sqlfrom 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) |