使用一个查询创建汇总结果

Question

我有一个具有以下格式的表格。

mysql> describe unit_characteristics;
+----------------------+------------------+------+-----+---------+----------------+
| Field                | Type             | Null | Key | Default | Extra          |
+----------------------+------------------+------+-----+---------+----------------+
| id                   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| uut_id               | int(10) unsigned | NO   | PRI | NULL    |                |
| uut_sn               | varchar(45)      | NO   |     | NULL    |                |
| characteristic_name  | varchar(80)      | NO   | PRI | NULL    |                |
| characteristic_value | text             | NO   |     | NULL    |                |
| creation_time        | datetime         | NO   |     | NULL    |                |
| last_modified_time   | datetime         | NO   |     | NULL    |                |
+----------------------+------------------+------+-----+---------+----------------+

每个uut_sn有多个characteristic_name/value对。我想使用 MySQL 生成一张表，

+----------------------+-------------+-------------+-------------+--------------+
| uut_sn | char_name_1 | char_name_2 | char_name_3 | char_name_4 | ...          |
+----------------------+-------------+-------------+-------------+--------------+
| 00000  | char_val_1  | char_val_2  | char_val_3  | char_val_4  | ...          | 
| 00001  | char_val_1  | char_val_2  | char_val_3  | char_val_4  | ...          |
| 00002  | char_val_1  | char_val_2  | char_val_3  | char_val_4  | ...          |
| .....  | char_val_1  | char_val_2  | char_val_3  | char_val_4  | ...          |
+----------------------+------------------+------+-----+---------+--------------+

只需要一个查询就可以实现吗？

谢谢， -彼得

Answer 1

您正在使用 EAV 反模式。如果不硬编码您想要包含的特征，则无法自动生成您描述的数据透视表。正如 @OMG Ponies 提到的，您需要使用动态 SQL 以自定义方式通用查询，以获得您想要包含在结果中的特征集。

相反，我建议您获取每行一个特征，因为它们存储在数据库中，并且如果您希望应用程序对象代表单个 UUT 及其所有特征，您可以编写代码在获取行时循环遍历这些行。您的应用程序，将它们收集到对象中。

例如，在 PHP 中：

$sql = "SELECT uut_sn, characteristic_name, characteristic_value 
        FROM unit_characteristics";
$stmt = $pdo->query($sql);

$objects = array();
while ($row = $stmt->fetch()) {
  if (!isset($objects[ $row["uut_sn"] ])) {
      $object[ $row["uut_sn"] ] = new Uut();
  }
  $objects[ $row["uut_sn"] ]->$row["characteristic_name"] 
                            = $row["characterstic_value"];
}

与在查询中硬编码特征名称的解决方案相比，这具有一些优势：

• 该解决方案仅需要一个 SQL 查询，而不是两个。
• 构建动态 SQL 查询不需要复杂的代码。
• 如果您忘记了其中一个特征，该解决方案无论如何都会自动找到它。
• MySQL 中的 GROUP BY 通常很慢，这避免了 GROUP BY。

Answer 2

这是一个标准的数据透视查询：

  SELECT uc.uut_sn,
         MAX(CASE 
               WHEN uc.characteristic_name = 'char_name_1' THEN uc.characteristic_value 
               ELSE NULL 
             END) AS char_name_1,
         MAX(CASE 
               WHEN uc.characteristic_name = 'char_name_2' THEN uc.characteristic_value 
               ELSE NULL 
             END) AS char_name_2,
         MAX(CASE 
               WHEN uc.characteristic_name = 'char_name_3' THEN uc.characteristic_value 
               ELSE NULL 
             END) AS char_name_3,
    FROM unit_characteristics uc
GROUP BY uc.uut_sn

要使其动态，您需要使用 MySQL 的动态 SQL 语法称为预准备语句。它需要两个查询 - 第一个查询获取 characteristic_name 值的列表，因此您可以将适当的字符串连接到 CASE 表达式中，就像您在我的示例中看到的最终查询一样。