我有一个格式如下的表格.
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.
更多推荐
使用一个查询创建汇总结果
发布评论