动态数据透视表pivot table(行转换列)
来源:互联网 发布:酷云eye收视数据猎场 编辑:程序博客网 时间:2024/05/18 12:04
原文地址:http://buysql.com/mysql/14-how-to-automate-pivot-tables.html
MYSQL下如何动态生成标题的数据透视表。
先用以下script生成一张包含若干属性的表叫"properties"。
CREATE TABLE properties ( id INT(11) NOT NULL AUTO_INCREMENT, item_id INT(11) DEFAULT NULL, property_name VARCHAR(255) DEFAULT NULL, value VARCHAR(255) DEFAULT NULL, PRIMARY KEY (id));INSERT INTO properties VALUES (1, 1, 'color', 'blue'), (2, 1, 'size', 'large'), (3, 1, 'weight', 65), (4, 2, 'color', 'orange'), (5, 2, 'weight', 57), (6, 2, 'size', 'large'), (7, 3, 'size', 'small'), (8, 3, 'color', 'red'), (9, 3, 'weight', 12), (10, 4, 'color', 'violet'), (11, 4, 'size', 'medium'), (12, 4, 'weight', 34), (13, 5, 'color', 'green'), (14, 5, 'weight', 10);
然后我们想根据原始数据生成如下report。
+----+---------+---------------+--------+| id | item_id | property_name | value |+----+---------+---------------+--------+| 1 | 1 | color | blue || 2 | 1 | size | large || 3 | 1 | weight | 65 || 4 | 2 | color | orange || 5 | 2 | weight | 57 || 6 | 2 | size | large || 7 | 3 | size | small || 8 | 3 | color | red || 9 | 3 | weight | 12 || 10 | 4 | color | violet || 11 | 4 | size | medium || 12 | 4 | weight | 34 || 13 | 5 | color | green || 14 | 5 | weight | 10 |+----+---------+---------------+--------+
=>+---------+--------+--------+--------+| item_id | color | size | weight |+---------+--------+--------+--------+| 1 | blue | large | 65 || 2 | orange | large | 57 || 3 | red | small | 12 || 4 | violet | medium | 34 || 5 | green | NULL | 10 |+---------+--------+--------+--------+
众所周知,MySql里并没有自动表转换的功能。当然,我们可以用一些额外的程序或工具来连接MySQL去执行数据转换。但在这里,我们来探讨下如何手动的写一个查询来实现数据转换。该查询可以这样实现:
SELECT item_id, MAX(IF(property_name = 'color', value, NULL)) AS color, MAX(IF(property_name = 'size', value, NULL)) AS size, ... ... ...FROM propertiesGROUP BY item_id;
很明显,对于任何一个'property_name'我们都做了定义,如'color', 'size'。如果属性的类型不变的话,这个查询已经没有问题了。但是如果字段'property_name'经常变,并且可能会增加一个新的,那要怎么处理呢?难道只能每次去改这个查询语句么?这种情况下,就可以使用动态生成查询语句——读取表中字段'property_name'的所有值,根据该值动态的创建一个查询语句。
动态生成查询的实现如下:
SET @sql = NULL;SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(property_name = ''', property_name, ''', value, NULL)) AS ', property_name ) ) INTO @sqlFROM properties;SET @sql = CONCAT('SELECT item_id, ', @sql, ' FROM properties GROUP BY item_id');
执行该语句,会生成如下的查询语句(为了阅读方便,调整了下格式):
SELECT item_id, MAX(IF(property_name = 'color', value, NULL)) AS color, MAX(IF(property_name = 'size', value, NULL)) AS size, MAX(IF(property_name = 'weight', value, NULL)) AS weightFROM propertiesGROUP BY item_id
注意:
MySQL对于GROUP_CONCAT结果用系统变量group_concat_max_len做了限制,默认值是1024。所以,如果你的表有很多列,最好将该值设大一些。
SET @@group_concat_max_len = 5000;SELECT GROUP_CONCAT(column_name) FROM table;
经过执行动态生成语句,查询语句被写到变量@sql里,现在我们可以用prepared statment来执行该语句:
PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;
结果:
+---------+--------+--------+--------+| item_id | color | size | weight |+---------+--------+--------+--------+| 1 | blue | large | 65 || 2 | orange | large | 57 || 3 | red | small | 12 || 4 | violet | medium | 34 || 5 | green | NULL | 10 |+---------+--------+--------+--------+
0 0
- 动态数据透视表pivot table(行转换列)
- Excel 中使用数据透视表 Pivot Table
- Spotfire使用经验-在Cross Table(Pivot Table,透视表)中动态计算比例
- 动态SQL解决PIVOT透视多列的问题
- 通过sql做数据透视表,数据库表行列转换(pivot和Unpivot用法)(一)
- SQL 2008行列转换的pivot--产生动态列
- Python使用pandas扩展库DataFrame对象的pivot方法对数据进行透视转换
- SQL SERVER透视转换(行旋转列)
- springmvc+easyui实现透视表-动态生成列
- PIVOT 实现数据库中行列转换
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- struts2文件上传与错误信息国际化
- [Leetcode] Reverse Words in a String
- HDU 1856 More is better
- mysql 乱码
- 慢慢聊后台进程
- 动态数据透视表pivot table(行转换列)
- adb devices no permissions
- PHP异步并行扩展Swoole发布1.7版本
- oracle中 sql%rowcount 用法
- sockaddr_in 和sockaddr_un的区别
- 黑马程序员_java基础下
- 宝马车 三种版本的不同隐藏功能,看看您开启了吗
- 微信公众平台开发入门教程(图文详解)
- Eigen函数与Matlab函数的对应关系