MySQL Join 元表(WordPress 风格的表结构)

Question

也许这是非常基本的，这就是为什么我没有找到任何东西的原因......

这是我的表结构:

对象

id | type    | …
1  | brand   | …
2  | project | …
3  | brand   | …

元

id | object | name  | value
1  | 1      | name  | adidas
2  | 1      | color | blue
3  | 3      | name  | telekom
4  | 3      | color | pink

我想获取类似于以下内容的数据:

SELECT o.*, EACH( m.value as m.name ) FROM object o LEFT JOIN meta m ON m.object = o.id GROUP BY id

这应该导致:

id | type  | … | name    | color
1  | brand | … | adidas  | blue
2  | brand | … | telekom | pink

有什么想法吗？这必须是动态的，因为元行的数量及其键可能不同。

我认为这与 WordPress 的表结构(帖子和帖子元)非常接近……他们是在初始查询中获取元数据，还是为每个选择元的结果触发第二个查询？

Answer 1

<强> SQL DEMO

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(m.name = ''',
      name,
      ''', m.value, NULL)) AS ',
      name
    )
  ) INTO @sql
FROM meta m;

@sql

MAX(IF(m.name = 'name', m.value, NULL)) AS name,
MAX(IF(m.name = 'color', m.value, NULL)) AS color

.

SET @sql = CONCAT('SELECT o.`id`
                    , o.`type`
                    , o.`description`, ', @sql, ' 
                   FROM object o
                   LEFT JOIN meta AS m
                    ON o.`id` = m.`object`
                   GROUP BY o.id');

@sql

SELECT o.`id`
     , o.`type`
     , o.`description`
     , MAX(IF(m.name = 'name', m.value, NULL)) AS name
     , MAX(IF(m.name = 'color', m.value, NULL)) AS color 
FROM object o
LEFT JOIN meta AS m
  ON o.`id` = m.`object`
GROUP BY o.id

.

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

输出

+----+---------+-------------+---------+-------+
| id |  type   | description |  name   | color |
+----+---------+-------------+---------+-------+
|  1 | brand   | a           | adidas  | blue  |
|  2 | project | b           |         |       |
|  3 | brand   | c           | telekom | pink  |
+----+---------+-------------+---------+-------+