MySQL 需要帮助将垂直数据格式化为水平数据

Question

目前数据库看起来像这样:(带有名称值对的产品 ID)

id, attribute_name, attribute_value
1, 主频, 1.6Ghz
1, 屏幕, 13.3"
2, 主频, 1.8Ghz
2、屏幕，15.1"

---

我想将以上数据转换为以下格式(以产品ID分隔，每个ID只有一行)以便迁移到新平台。

id、时钟速度、屏幕
1, 1.6Ghz, 13.3"
2, 1.8Ghz, 15.1"

实现此结果的最简单方法是什么？我的直觉告诉我这将通过 concat 或 group_concat 函数来完成，但我需要指向正确的方向，因为我的头发被拔掉了。

Answer 1

这指出了 entity-attribute-value 的问题之一数据库设计。

有两种方法可以使用 SQL 将属性转换为列，就像您已将数据存储在常规表中一样:

SELECT id, MAX(CASE attribute_name WHEN 'Clockspeed' THEN attribute_value END) AS Clockspeed,
  MAX(CASE attribute_name WHEN 'Screen' THEN attribute_value END) AS Screen
FROM eav_table
GROUP BY id;

SELECT id, c.attribute_value AS Clockspeed, s.attribute_value AS Screen
FROM eav_table AS c
JOIN eav_table AS s USING(id)
WHERE c.attribute_name = 'Clockspeed' AND s.attribute_name = 'Screen'

在 MySQL 5.6 上测试后两个查询的输出:

+------+------------+--------+
| id   | Clockspeed | Screen |
+------+------------+--------+
|    1 | 1.6GHz     | 13.3"  |
|    2 | 1.8GHz     | 15.1"  |
+------+------------+--------+

后一种解决方案需要 N-1 个连接才能输出 N 个属性。它不能很好地扩展。

上述两种解决方案都需要您根据要获取的属性数量编写相当多的应用程序代码来格式化 SQL 查询。这意味着如果属性的数量发生变化(这很可能是因为这是使用 EAV 的主要优势之一)，那么查询可能会获取过多的属性而无法获得良好的性能。

另一种解决方案是忘记仅使用 SQL 来透视数据。相反，将数据库行提取到您的应用程序中，每行一个属性，因为它们存储在数据库中。然后编写应用程序代码将结果后处理到一个对象中。