mysql - 如何根据联接表值选择 "fake"列/值？

Question

简短版本:

我需要自定义查询结果，使用一个值作为列名，另一个值作为列的值。它还需要是自动的(不是硬编码，数百个可能的“元键”)。

长版:

我正在使用 Wordpress 的“用户元”结构，其中单个用户有一个用户 ID，另一个表包含与用户相关的“元数据”。

这个元数据是任意的，只有四列:

meta_id、post_id、meta_key、meta_value。

我想为某个用户执行 SQL 查询，并格式化结果，以便所有元数据都被格式化为行，其中 meta_key 是列名，并且meta_value 是列的值。

---

示例数据库:

-- wp_users
* ID    | username | email
35      | radgh    | radgh@example.org

-- wp_usermeta
* meta_id | user_id | meta_key   | meta_value
1         | 35      | first-name | Radley
2         | 35      | last-name  | Sustaire
3         | 35      | newsletter | on

我的查询(我需要弄清楚 (???) 部分，这将生成元键/值对作为假列)

 SELECT 
    `users`.ID as 'user_id',
    `users`.username as 'username',
    `users`.email as 'email',
    (???)

 FROM `wp_users` `users`
     INNER JOIN `wp_usermeta` `meta`
     ON `users`.ID = `meta`.user_id

 WHERE `wp_users`.ID = 35

期望的结果:

* user_id | username | email             | first-name | last-name | newsletter
35        | radgh    | radgh@example.org | Radley     | Sustaire  | on

在此similar question所选答案使用硬编码字段。我想自动执行此操作。 :)

Answer 1

MySQL 没有数据透视表函数。如果您已经知道 meta_key 的所有可能值，则可以使用这样的硬编码查询:

SELECT
  wp_users.*,
  MAX(CASE WHEN `meta_key`='first-name' THEN meta_value END) AS `first-name`,
  MAX(CASE WHEN `meta_key`='last-name' THEN meta_value END) AS `last-name`,
  ....
FROM
  wp_users INNER JOIN wp_usermeta
  ON wp_users.ID=wp_usermeta.user_id
GROUP BY
  wp_users.ID, wp_users.username, wp_users.email;

或者您可以使用准备好的语句动态创建 SQL 查询，如下所示:

SELECT
  CONCAT(
    'SELECT wp_users.*,',
    GROUP_CONCAT(
      CONCAT(
        'MAX(CASE WHEN `meta_key`=\'',
        meta_key,
        '\' THEN meta_value END) AS `',
        meta_key,
        '`')),
    ' FROM wp_users INNER JOIN wp_usermeta ON wp_users.ID=wp_usermeta.user_id',
    ' GROUP BY wp_users.ID, wp_users.username, wp_users.email')
FROM
  (SELECT DISTINCT meta_key FROM wp_usermeta) s
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

请参阅 fiddle here .