집 >데이터 베이스 >MySQL 튜토리얼 >[MySQL]行列转换变化各种方法实现总结(行变列报表统计、列变行数据记录统计等)_MySQL
前言:
mysql行列变化,最难的就是将多个列变成多行,使用的比较多的是统计学中行变列,列变行,没有找到现成的函数或者语句,所以自己写了存储过程,使用动态sql来实现,应用业务场景,用户每个月都有使用记录数录入一张表,一个月一个字段,所以表的字段是动态增长的,现在需要实时统计当前用户使用的总数量,如果你知道有多少个字段,那么可以用select c1+c2+c3+…. From tbname where tid=’111’;来实现,但是关键是这个都是动态的,所以在应用程序端来实现确实不适宜,可以放在数据库后台在存储过程里实现。
而且在行变成列中,如果要写单个sql来实现,列的数目就需要写死,因为如果不知道要展示成多少列的话,就需要用动态变量,而一条sql里面无法使用动态变量。但是可以使用sql块来实现动态的效果。
这是基础数据表,里面有多个字段wm201403……,现在需要把N个这样的列变成行数据。
USE csdn; DROP TABLE IF EXISTS flow_table; CREATE TABLE `flow_table` ( `ID` INT(11) NOT NULL AUTO_INCREMENT, `Number` BIGINT(11) NOT NULL, `City` VARCHAR(10) NOT NULL, `wm201403` DECIMAL(7,2) DEFAULT NULL, `wm201404` DECIMAL(7,2) DEFAULT NULL, `wm201405` DECIMAL(7,2) DEFAULT NULL, `wm201406` DECIMAL(7,2) DEFAULT NULL, `wm201407` DECIMAL(7,2) DEFAULT NULL, `wm201408` DECIMAL(7,2) DEFAULT NULL, PRIMARY KEY (`ID`,`Number`) ) ENGINE=INNODB DEFAULT CHARSET=utf8;
录入一批测试数据:
INSERT INTO flow_table(Number,City,wm201403,wm201404,wm201405,wm201406,wm201407,wm201408)SELECT 1,'shanghai',100.2,180.4,141,164,124,127; INSERT INTO flow_table(Number,City,wm201403,wm201404,wm201405,wm201406,wm201407,wm201408)SELECT 2,'shanghai',110.23,180.34,141.23,104.78,124.67,127.45; INSERT INTO flow_table(Number,City,wm201403,wm201404,wm201405,wm201406,wm201407,wm201408)SELECT 3,'beijing',123.23,110.34,131.33,154.58,154.67,167.45; INSERT INTO flow_table(Number,City,wm201403,wm201404,wm201405,wm201406,wm201407,wm201408)SELECT 4,'hangzhou',0,110.34,131.33,154.58,154.67,0; INSERT INTO flow_table(Number,City,wm201405,wm201406,wm201407,wm201408)SELECT 5,'hangzhou',131.33,154.58,154.67,0;
需要达到的统计效果是:
+--------+-----------+
| Number | total_num |
+--------+-----------+
| 1 | 836.60 |
| 2 | 788.70 |
| 3 | 841.60 |
| 4 | 550.92 |
| 5 | 440.58 |
+--------+-----------+
5 rows in set (0.00 sec)
这个存储过程建立了2张临时表,查询测试表数据形成游标,遍历游标根据主键Number来调用pro_flow_modify存储过程进行行列变化。代码如下:
DELIMITER $$ DROP PROCEDURE IF EXISTS csdn.`proc_all_changes`$$ CREATE PROCEDURE csdn.proc_all_changes() BEGIN DECLARE v_number BIGINT; DECLARE v_city VARCHAR(10); DECLARE _done INT DEFAULT 0; /*定义游标*/ DECLARE cur_all CURSOR FOR SELECT Number,City FROM csdn.`flow_table`; /**这里如果需要定义下当NOT FOUND的时候,EXIT退出游标遍历,不然如果设置成CONTINUE会一直执行下去。*/ DECLARE EXIT HANDLER FOR NOT FOUND BEGIN SET _done=1;END; /*建立临时表,存放所有字段的临时表*/ DROP TABLE IF EXISTS flow_n_columns; CREATE TABLE `flow_n_columns` ( `column_name` VARCHAR(10) NOT NULL ) ENGINE=INNODB DEFAULT CHARSET=utf8; /*存放最终变成行的数据表*/ DROP TABLE IF EXISTS flow_tmp; CREATE TABLE `flow_tmp` ( `Number` INT(11) DEFAULT NULL, `City` VARCHAR(10) DEFAULT NULL, `wm_str` VARCHAR(10) DEFAULT NULL, `Wm` DECIMAL(7,2) DEFAULT NULL ) ENGINE=INNODB DEFAULT CHARSET=utf8; OPEN cur_all; REPEAT FETCH cur_all INTO v_number, v_city; IF NOT _done THEN CALL csdn.pro_flow_modify(v_number,v_city); END IF; UNTIL _done=1 END REPEAT; CLOSE cur_all; /*展示下所有的行转列的数据**/ SELECT * FROM csdn.flow_tmp; END$$ DELIMITER ;
通过查询系统表information_schema.`COLUMNS`来获取测试表flow_table的所有列,然后写动态SQL,来把列的值录入到临时表flow_tmp中。
DELIMITER $$ DROP PROCEDURE IF EXISTS csdn.`pro_flow_modify`$$ CREATE PROCEDURE csdn.`pro_flow_modify`(p_Number INT,p_city VARCHAR(10)) BEGIN DECLARE v_column_name VARCHAR(10) DEFAULT ''; DECLARE v_exe_sql VARCHAR(1000) DEFAULT ''; DECLARE v_start_wm VARCHAR(10) DEFAULT ''; DECLARE v_end_wm VARCHAR(10) DEFAULT ''; DECLARE v_num DECIMAL(10,2) DEFAULT 0; DECLARE i INT DEFAULT 1; DECLARE v_Number INT DEFAULT 0; SET v_Number=p_Number; DELETE FROM csdn.flow_n_columns; DELETE FROM csdn.flow_tmp WHERE Number=v_Number; /*把测试表flow_table的所有字段都录入字段临时表中,这样就达到了从列变成行的目的*/ INSERT INTO flow_n_columns SELECT t.`COLUMN_NAME` FROM information_schema.`COLUMNS` t WHERE t.`TABLE_NAME`='flow_table' AND t.`TABLE_SCHEMA`='csdn' AND t.`COLUMN_NAME` NOT IN('ID','Number','City'); SELECT column_name INTO v_column_name FROM csdn.flow_n_columns LIMIT 1; /*开始循环遍历字段临时表的字段数据,并且把字段值放入临时表flow_tmp里面*/ WHILE i>0 DO SET v_exe_sql=CONCAT('INSERT INTO csdn.flow_tmp(Number,City,wm_str,Wm) select ',v_Number,',\'',p_city, '\',\'',v_column_name,'\',',v_column_name,' from csdn.flow_table WHERE flow_table.Number=',v_Number,';'); SET @sql=v_exe_sql; PREPARE s1 FROM @sql; EXECUTE s1; DEALLOCATE PREPARE s1; DELETE FROM csdn.flow_n_columns WHERE column_name=v_column_name; SELECT column_name INTO v_column_name FROM csdn.flow_n_columns LIMIT 1; SELECT COUNT(1) INTO i FROM csdn.flow_n_columns ; DELETE FROM csdn.flow_tmp WHERE Wm=0; END WHILE; /*由于触发器是不支持动态sql,所以不能使用while循环,动态遍历所有统计列的,只能写死列了,如下所示: 现在一个个insert只能写死了, flow_table表有多少个统计列就写多少个insert sql,以后新添加一个列,就在这里新添加一条insertsql语句 INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201403',wm201403 FROM flow_table WHERE Number=v_Number ; INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201404',wm201404 FROM flow_table WHERE Number=v_Number ; INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201405',wm201405 FROM flow_table WHERE Number=v_Number ; INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201406',wm201406 FROM flow_table WHERE Number=v_Number ; INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201407',wm201407 FROM flow_table WHERE Number=v_Number ; INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201408',wm201408 FROM flow_table WHERE Number=v_Number ; */ /*清除掉不数据=0的列*/ DELETE FROM csdn.flow_tmp WHERE Wm=0 OR Wm IS NULL; SELECT wm_str INTO v_start_wm FROM csdn.flow_tmp WHERE Number=v_Number ORDER BY wm_str ASC LIMIT 1; SELECT wm_str INTO v_end_wm FROM csdn.flow_tmp WHERE Number=v_Number ORDER BY wm_str DESC LIMIT 1; SELECT SUM(Wm) INTO v_num FROM csdn.flow_tmp WHERE Number=v_Number; END$$ DELIMITER ;
临时表的所有数据:
mysql> SELECT * FROM csdn.flow_tmp; +--------+----------+----------+--------+ | Number | City | wm_str | Wm | +--------+----------+----------+--------+ | 1 | shanghai | wm201403 | 100.20 | | 1 | shanghai | wm201404 | 180.40 | | 1 | shanghai | wm201405 | 141.00 | | 1 | shanghai | wm201406 | 164.00 | | 1 | shanghai | wm201407 | 124.00 | | 1 | shanghai | wm201408 | 127.00 | | 2 | shanghai | wm201403 | 110.23 | | 2 | shanghai | wm201404 | 180.34 | | 2 | shanghai | wm201405 | 141.23 | | 2 | shanghai | wm201406 | 104.78 | | 2 | shanghai | wm201407 | 124.67 | | 2 | shanghai | wm201408 | 127.45 | | 3 | beijing | wm201403 | 123.23 | | 3 | beijing | wm201404 | 110.34 | | 3 | beijing | wm201405 | 131.33 | | 3 | beijing | wm201406 | 154.58 | | 3 | beijing | wm201407 | 154.67 | | 3 | beijing | wm201408 | 167.45 | | 4 | hangzhou | wm201404 | 110.34 | | 4 | hangzhou | wm201405 | 131.33 | | 4 | hangzhou | wm201406 | 154.58 | | 4 | hangzhou | wm201407 | 154.67 | | 5 | hangzhou | wm201405 | 131.33 | | 5 | hangzhou | wm201406 | 154.58 | | 5 | hangzhou | wm201407 | 154.67 | +--------+----------+----------+--------+ 25 rows in set (0.00 sec) mysql>
统计每个用户的使用总量为:
mysql> SELECT Number,SUM(Wm) 'total_num' FROM flow_tmp GROUP BY Number ORDER BY Number; +--------+-----------+ | Number | total_num | +--------+-----------+ | 1 | 836.60 | | 2 | 788.70 | | 3 | 841.60 | | 4 | 550.92 | | 5 | 440.58 | +--------+-----------+ 5 rows in set (0.00 sec) mysql>
USE csdn; DROP TABLE IF EXISTS csdn.tb; CREATE TABLE tb(`cname` VARCHAR(10),cource VARCHAR(10),score INT) ENGINE=INNODB; INSERT INTO tb VALUES('张三','语文',74); INSERT INTO tb VALUES('张三','数学',83); INSERT INTO tb VALUES('张三','物理',93); INSERT INTO tb VALUES('李四','语文',74); INSERT INTO tb VALUES('李四','数学',84); INSERT INTO tb VALUES('李四','物理',94); SELECT * FROM tb;需要得到的结果是:
+--------------------+--------+--------+--------+-----------+--------------+
| 姓名 | 语文 | 数学 | 物理 | 总成绩 | 平均成绩 |
+--------------------+--------+--------+--------+-----------+--------------+
| 张三 | 74.00 | 83.00 | 93.00 | 250.00 | 83.33 |
| 李四 | 74.00 | 84.00 | 94.00 | 252.00 | 84.00 |
| 总成绩平均数 | 74.00 | 83.50 | 93.50 | 251.00 | 83.67 |
+--------------------+--------+--------+--------+-----------+--------------+
SQL代码块如下:
SELECT cname AS "姓名", SUM(IF(cource="语文",score,0)) AS "语文", SUM(IF(cource="数学",score,0)) AS "数学", SUM(IF(cource="物理",score,0)) AS "物理", SUM(score) AS "总成绩", ROUND(AVG(score),2) AS "平均成绩" FROM tb GROUP BY cname UNION ALL SELECT "总成绩平均数", ROUND(AVG(`语文`),2) , ROUND(AVG(`数学`),2), ROUND(AVG(`物理`),2), ROUND(AVG(`总成绩`),2), ROUND(AVG(`平均成绩`),2) FROM( SELECT "all",cname AS "姓名", SUM(IF(cource="语文",score,0)) AS "语文", SUM(IF(cource="数学",score,0)) AS "数学", SUM(IF(cource="物理",score,0)) AS "物理", SUM(score) AS "总成绩", AVG(score) AS "平均成绩" FROM tb GROUP BY cname )tb2 GROUP BY tb2.all;
执行结果正确,如下所示:
+--------------------+--------+--------+--------+-----------+--------------+
| 姓名 | 语文 | 数学 | 物理 | 总成绩 | 平均成绩 |
+--------------------+--------+--------+--------+-----------+--------------+
| 张三 | 74.00 | 83.00 | 93.00 | 250.00 | 83.33 |
| 李四 | 74.00 | 84.00 | 94.00 | 252.00 | 84.00 |
| 总成绩平均数 | 74.00 | 83.50 | 93.50 | 251.00 | 83.67 |
+--------------------+--------+--------+--------+-----------+--------------+
SQL代码如下:
SELECT cname AS "姓名", MAX(CASE cource WHEN "语文" THEN score ELSE 0 END) AS "语文", MAX(CASE cource WHEN "数学" THEN score ELSE 0 END) AS "数学", MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理", SUM(score) AS "总成绩", ROUND(AVG(score) ,2) AS "平均成绩" FROM tb GROUP BY `cname` UNION ALL SELECT "总成绩平均数", ROUND(AVG(`语文`),2) , ROUND(AVG(`数学`),2), ROUND(AVG(`物理`),2), ROUND(AVG(`总成绩`),2), ROUND(AVG(`平均成绩`),2) FROM( SELECT 'all' , cname AS "姓名", MAX(CASE cource WHEN "语文" THEN score ELSE 0 END) AS "语文", MAX(CASE cource WHEN "数学" THEN score ELSE 0 END) AS "数学", MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理", SUM(score) AS "总成绩", ROUND(AVG(score) ,2) AS "平均成绩" FROM tb GROUP BY `cname` )tb2 GROUP BY tb2.all
执行结果正确,如下所示:
+--------------------+--------+--------+--------+-----------+--------------+
| 姓名 | 语文 | 数学 | 物理 | 总成绩 | 平均成绩 |
+--------------------+--------+--------+--------+-----------+--------------+
| 张三 | 74.00 | 83.00 | 93.00 | 250.00 | 83.33 |
| 李四 | 74.00 | 84.00 | 94.00 | 252.00 | 84.00 |
| 总成绩平均数 | 74.00 | 83.50 | 93.50 | 251.00 | 83.67 |
+--------------------+--------+--------+--------+-----------+--------------+
SELECT IFNULL(cname,'总平均数') AS "姓名", MAX(CASE cource WHEN "语文" THEN score ELSE 0 END) AS "语文", MAX(CASE cource WHEN "数学" THEN score ELSE 0 END) AS "数学", MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理", ROUND(AVG(score),2) AS "总成绩", ROUND(AVG(avg_score),2) AS "平均成绩" FROM( SELECT cname , IFNULL(cource,'total') cource, SUM(score) AS score, ROUND(AVG(score) ,2) AS avg_score FROM tb GROUP BY `cname`,cource WITH ROLLUP HAVING cname IS NOT NULL )tb2 GROUP BY tb2.cname WITH ROLLUP;
mysql> SELECT IFNULL(cname,'总平均数') AS "姓名", -> MAX(CASE cource WHEN "语文" THEN score ELSE 0 END) AS "语文", -> MAX(CASE cource WHEN "数学" THEN score ELSE 0 END) AS "数学", -> MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理", -> ROUND(AVG(score),2) AS "总成绩", -> ROUND(AVG(avg_score),2) AS "平均成绩" -> FROM( -> SELECT -> Display ALL 793 possibilities? (Y OR n) -> cname , -> Display ALL 793 possibilities? (Y OR n) -> IFNULL(cource,'total') cource, -> Display ALL 793 possibilities? (Y OR n) -> SUM(score) AS score, -> Display ALL 793 possibilities? (Y OR n) -> ROUND(AVG(score) ,2) AS avg_score -> FROM tb -> GROUP BY `cname`,cource WITH ROLLUP HAVING cname IS NOT NULL -> )tb2 -> GROUP BY tb2.cname WITH ROLLUP; +--------------+--------+--------+--------+-----------+--------------+ | 姓名 | 语文 | 数学 | 物理 | 总成绩 | 平均成绩 | +--------------+--------+--------+--------+-----------+--------------+ | 张三 | 74 | 83 | 93 | 125.00 | 83.33 | | 李四 | 74 | 84 | 94 | 126.00 | 84.00 | | 总平均数 | 74 | 84 | 94 | 125.50 | 83.67 | +--------------+--------+--------+--------+-----------+--------------+ 3 ROWS IN SET, 1 warning (0.00 sec) mysql>
总结: WITH rollup中对求列的总数是OK的,但是求列的平均数有偏差,这里场景使用不是恰当。
SQL代码块如下:
/*仅仅班级成员部分*/ SET @a=''; SELECT @a:=CONCAT(@a,'SUM(IF(cource=\'',cource,'\'',',score,0)) AS ',cource,',') FROM (SELECT DISTINCT cource FROM tb) A; SET @a=CONCAT(@a,"ROUND(AVG(score) ,2) AS \"平均成绩\""); SET @b=CONCAT('SELECT IFNULL(cname,\'总成绩\'),',LEFT(@a,LENGTH(@a)-1),' ,SUM(score) AS \"总成绩\" FROM tb GROUP BY cname '); /*班级成员总计部分**/ SET @a2=""; SET @b2=CONCAT('SELECT "all",IFNULL(cname,\'总成绩\'),',LEFT(@a,LENGTH(@a)-1),' ,SUM(score) AS \"总成绩\" FROM tb GROUP BY cname '); SELECT @a2:=CONCAT(@a2,'ROUND(AVG(`',cource,'`),2),') FROM (SELECT DISTINCT cource FROM tb) A; SET @a2=CONCAT(@a2," ROUND(AVG(`平均成绩`),2),ROUND(AVG(`总成绩`),2) "); SET @c=CONCAT("SELECT \"班级平均数\",",LEFT(@a2,LENGTH(@a)-1)," FROM(",@b2,")tb2 GROUP BY tb2.all;"); SET @d=CONCAT(@b," UNION ALL ",@c); PREPARE stmt1 FROM @d; EXECUTE stmt1;
查看执行结果如下,已经达到效果:
mysql> /*仅仅班级成员部分*/ mysql> SET @a=''; QUERY OK, 0 ROWS affected (0.00 sec) mysql> SELECT @a:=CONCAT(@a,'SUM(IF(cource=\'',cource,'\'',',score,0)) AS ',cource,',') FROM (SELECT DISTINCT cource FROM tb) A; +-----------------------------------------------------------------------------------------------------------------------------------+ | @a:=CONCAT(@a,'SUM(IF(cource=\'',cource,'\'',',score,0)) AS ',cource,',') | +-----------------------------------------------------------------------------------------------------------------------------------+ | SUM(IF(cource='语文',score,0)) AS 语文, | | SUM(IF(cource='语文',score,0)) AS 语文,SUM(IF(cource='数学',score,0)) AS 数学, | | SUM(IF(cource='语文',score,0)) AS 语文,SUM(IF(cource='数学',score,0)) AS 数学,SUM(IF(cource='物理',score,0)) AS 物理, | +-----------------------------------------------------------------------------------------------------------------------------------+ 3 ROWS IN SET (0.00 sec) mysql> SET @a=CONCAT(@a,"ROUND(AVG(score) ,2) AS \"平均成绩\""); QUERY OK, 0 ROWS affected (0.00 sec) mysql> SET @b=CONCAT('SELECT IFNULL(cname,\'总成绩\'),',LEFT(@a,LENGTH(@a)-1),' ,SUM(score) AS \"总成绩\" FROM tb GROUP BY cname '); QUERY OK, 0 ROWS affected (0.00 sec) mysql> mysql> /*班级成员总计部分**/ mysql> SET @a2=""; QUERY OK, 0 ROWS affected (0.00 sec) mysql> SET @b2=CONCAT('SELECT "all",IFNULL(cname,\'总成绩\'),',LEFT(@a,LENGTH(@a)-1),' ,SUM(score) AS \"总成绩\" FROM tb GROUP BY cname '); QUERY OK, 0 ROWS affected (0.00 sec) mysql> SELECT @a2:=CONCAT(@a2,'ROUND(AVG(`',cource,'`),2),') FROM (SELECT DISTINCT cource FROM tb) A; +-----------------------------------------------------------------------+ | @a2:=CONCAT(@a2,'ROUND(AVG(`',cource,'`),2),') | +-----------------------------------------------------------------------+ | ROUND(AVG(`语文`),2), | | ROUND(AVG(`语文`),2),ROUND(AVG(`数学`),2), | | ROUND(AVG(`语文`),2),ROUND(AVG(`数学`),2),ROUND(AVG(`物理`),2), | +-----------------------------------------------------------------------+ 3 ROWS IN SET (0.00 sec) mysql> SET @a2=CONCAT(@a2," ROUND(AVG(`平均成绩`),2),ROUND(AVG(`总成绩`),2) "); QUERY OK, 0 ROWS affected (0.00 sec) mysql> SET @c=CONCAT("SELECT \"班级平均数\",",LEFT(@a2,LENGTH(@a)-1)," FROM(",@b2,")tb2 GROUP BY tb2.all;"); QUERY OK, 0 ROWS affected (0.00 sec) mysql> SET @d=CONCAT(@b," UNION ALL ",@c); QUERY OK, 0 ROWS affected (0.00 sec) mysql> mysql> PREPARE stmt1 FROM @d; QUERY OK, 0 ROWS affected (0.00 sec) Statement prepared mysql> EXECUTE stmt1; +---------------------------+--------+--------+--------+--------------+-----------+ | IFNULL(cname,'总成绩') | 语文 | 数学 | 物理 | 平均成绩 | 总成绩 | +---------------------------+--------+--------+--------+--------------+-----------+ | 张三 | 74.00 | 83.00 | 93.00 | 83.33 | 250.00 | | 李四 | 74.00 | 84.00 | 94.00 | 84.00 | 252.00 | | 班级平均数 | 74.00 | 83.50 | 93.50 | 83.67 | 251.00 | +---------------------------+--------+--------+--------+--------------+-----------+ 3 ROWS IN SET (0.00 sec) mysql>
参考文章地址:http://blog.chinaunix.net/uid-7692530-id-2567582.html