mysql - 选择带有元数据的行

Question

我有两张 table

表一

===================================
= Id = Name             =  email  =
===================================
= 1  = David            = d@d.com =
===================================

表2

==================================
= uid = key        =  Value      =
==================================
= 1   = Age        =  18         =
= 1   = Tele       =  0123456798 =
==================================

想法是 UID 与 Id 相关联以链接表。

我想做的是运行一个 SQL 查询来获得以下输出。

=========================================================================
= Id = Name             =  email  = age        =  Tele                  =
=========================================================================
= 1  = David            = d@d.com = 18         = 012345678              =
=========================================================================

我需要运行什么查询。

谢谢

Answer 1

虽然您已经接受了答案，但我发布此答案是为了提供替代解决方案。您正在尝试 PIVOT 数据，而 MySQL 没有数据透视函数，因此您可以使用带有 CASE 语句的聚合函数。

如果您有已知数量的值，则可以对查询进行硬编码:

select t1.id,
    t1.name,
    t1.email,
    MAX(CASE WHEN t2.key='age' THEN t2.value ELSE NULL END) AS age,
    MAX(CASE WHEN t2.key='tele' THEN t2.value ELSE NULL END) AS tele
FROM Table1 t1 
LEFT JOIN Table2 t2 
  ON t1.Id = t2.uid
GROUP BY t1.id, t1.name, t1.email

参见 SQL Fiddle with Demo

但是如果您有未知数量的key 值，那么您将需要使用准备好的语句或创建动态 sql 版本:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when t2.`key` = ''',
      `key`,
      ''' then t2.Value else null end) AS ',
      `key`
    )
  ) INTO @sql
FROM table2;

SET @sql = CONCAT('SELECT t1.id, t1.name, t1.email, ', @sql, ' 
                  FROM table1 t1
                  left join table2 t2
                    on t1.id = t2.uid
                  GROUP BY t1.id, t1.name, t1.email');

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

参见 SQL Fiddle with Demo

结果:

| ID |  NAME |   EMAIL | AGE |      TELE |
------------------------------------------
|  1 | David | d@d.com |  18 | 123456798 |

两者都会产生相同的结果，但准备好的语句版本会在运行时生成 key 值列表。