使用一个查询创建汇总结果 [英] Create a summary result with one query

问题描述

我有一个格式如下的表格.

I have a table with the following format.

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 都有多个特性名称/值对.我想用MySQL生成表

each uut_sn has multiple characteristic_name/value pairs. I want to use MySQL to generate a table

+----------------------+-------------+-------------+-------------+--------------+
| 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  | ...          |
+----------------------+------------------+------+-----+---------+--------------+

这可以通过一个查询实现吗?

Is this possible with just one query?

谢谢，-彼得

推荐答案

这是一个标准的透视查询:

This is a standard pivot query:

  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 语法称为 Prepared Statements.它需要两个查询 - 第一个获取 characteristic_name 值的列表，因此您可以将适当的字符串连接到 CASE 表达式中，就像您在我的示例中看到的最终查询一样.

To make it dynamic, you need to use MySQL's dynamic SQL syntax called Prepared Statements. It requires two queries - the first gets a list of the characteristic_name values, so you can concatenate the appropriate string into the CASE expressions like you see in my example as the ultimate query.

这篇关于使用一个查询创建汇总结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！