如何基于连接的表值选择“虚拟”列/值？

Question

如何基于连接的表值选择“虚拟”列/值？

mysqlwordpressselectinner-join

4

简短版：

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

详细版：

我正在使用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

在这个类似的问题中，所选答案使用硬编码字段。我想自动执行此操作。 :)

- Radley Sustaire

取出'myfakecolumn'并尝试应用此逻辑。[https://dev59.com/2m7Xa4cB1Zd3GeqPvfDt] - RazorSky

2个回答

2

它被称为实体-属性-值模型。针对MySQL中动态列（元数据）的简单/复杂查询解决方案可能无法实现（就像其他很棒的函数PIVOT在MySQL中缺失一样）。我认为唯一的方法是静态定义或者MySQL函数，从元表中获取可能的属性并构建正确的查询。

请看一下如何以关系表的形式检索MySQL EAV结果的最佳性能还要阅读有关EAV设计（优缺点）的Stack Entity-Attribute-Value Table Design的文章。

- kwarunek

有这么多术语需要学习，难怪我在尝试寻找类似问题时遇到了很多麻烦！我会对此进行一些研究，谢谢！也许可以通过执行单独的查询来查找每个元键，然后使用PHP将其格式化为“硬编码”布局的方式来解决这个问题，因为这可能是不可能的。 - Radley Sustaire

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

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

- fthiella · Accepted Answer

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 链接。