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

本文介绍了使用一个查询创建汇总结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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.