![[MySQL]行列转换变化各种方法实现总结（行变列报表统计 列变行数据记录统计等）【MySQL】](https://1200zi.500zi.com/uploadfile/img/14/559/2b78f03caecd1095455ded474a931283.jpg)
数据库|mysql教程
报表,行列,方法
数据库-mysql教程
前言：
七夕表白源码,ubuntu主题包设置,梦见消灭黑色爬虫,php php php,外部部seolzw
mysql行列变化，最难的就是将多个列变成多行，使用的比较多的是统计学中行变列，列变行，没有找到现成的函数或者语句，所以自己写了存储过程，使用动态sql来实现，应用业务场景，用户每个月都有使用记录数录入一张表，一个月一个字段，所以表的字段是动态增长的，现在需要实时统计当前用户使用的总数量，如果你知道有多少个字段，那么可以用select c1+c2+c3+…. From tbname where tid=’111’;来实现，但是关键是这个都是动态的，所以在应用程序端来实现确实不适宜，可以放在数据库后台在存储过程里实现。
学校源码php,vscode全局更改代码,ubuntu ros教程,小米路由 tomcat,安卓sqlite模糊查询,买 淘宝插件 购物返现,哪些前端框架可以做树型图,爬虫技术可能存在的危险,php wincache,江津区seo优化,网站会员推广功能,手机网页 触摸,phpwind 9.0模板lzw
而且在行变成列中，如果要写单个sql来实现，列的数目就需要写死，因为如果不知道要展示成多少列的话，就需要用动态变量，而一条sql里面无法使用动态变量。但是可以使用sql块来实现动态的效果。
微擎模块源码有网站,ubuntu用户自动登录,爬虫文件未响应,php 菜鸟,长沙网址seolzw
一，列变成行例子演示
1，准备测试数据
这是基础数据表，里面有多个字段wm03……，现在需要把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, `wm03` DECIMAL(7,2) DEFAULT NULL, `wm04` DECIMAL(7,2) DEFAULT NULL, `wm05` DECIMAL(7,2) DEFAULT NULL, `wm06` DECIMAL(7,2) DEFAULT NULL, `wm07` DECIMAL(7,2) DEFAULT NULL, `wm08` DECIMAL(7,2) DEFAULT NULL, PRIMARY KEY (`ID`,`Number`)) ENGINE=INNODB DEFAULT CHARSET=utf8;
录入一批测试数据：
INSERT INTO flow_table(Number,City,wm03,wm04,wm05,wm06,wm07,wm08)SELECT 1,shanghai,100.2,180.4,141,164,124,127;INSERT INTO flow_table(Number,City,wm03,wm04,wm05,wm06,wm07,wm08)SELECT 2,shanghai,110.23,180.34,141.23,104.78,124.67,127.45;INSERT INTO flow_table(Number,City,wm03,wm04,wm05,wm06,wm07,wm08)SELECT 3,eijing,123.23,110.34,131.33,154.58,154.67,167.45;INSERT INTO flow_table(Number,City,wm03,wm04,wm05,wm06,wm07,wm08)SELECT 4,hangzhou,0,110.34,131.33,154.58,154.67,0;INSERT INTO flow_table(Number,City,wm05,wm06,wm07,wm08)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，存储过程遍历：
这个存储过程建立了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 ;
3，行里变化存储过程
通过查询系统表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))BEGINDECLARE 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_columnsSELECT 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 DOSET 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,wm03,wm03 FROM flow_table WHERE Number=v_Number ;INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,wm04,wm04 FROM flow_table WHERE Number=v_Number ;INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,wm05,wm05 FROM flow_table WHERE Number=v_Number ;INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,wm06,wm06 FROM flow_table WHERE Number=v_Number ;INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,wm07,wm07 FROM flow_table WHERE Number=v_Number ;INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,wm08,wm08 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 ;
4，列变行结果展示
临时表的所有数据：
mysql> SELECT * FROM csdn.flow_tmp;+--------+----------+----------+--------+| Number | City| wm_str | Wm|+--------+----------+----------+--------+|1 | shanghai | wm03 | 100.20 ||1 | shanghai | wm04 | 180.40 ||1 | shanghai | wm05 | 141.00 ||1 | shanghai | wm06 | 164.00 ||1 | shanghai | wm07 | 124.00 ||1 | shanghai | wm08 | 127.00 ||2 | shanghai | wm03 | 110.23 ||2 | shanghai | wm04 | 180.34 ||2 | shanghai | wm05 | 141.23 ||2 | shanghai | wm06 | 104.78 ||2 | shanghai | wm07 | 124.67 ||2 | shanghai | wm08 | 127.45 ||3 | beijing | wm03 | 123.23 ||3 | beijing | wm04 | 110.34 ||3 | beijing | wm05 | 131.33 ||3 | beijing | wm06 | 154.58 ||3 | beijing | wm07 | 154.67 ||3 | beijing | wm08 | 167.45 ||4 | hangzhou | wm04 | 110.34 ||4 | hangzhou | wm05 | 131.33 ||4 | hangzhou | wm06 | 154.58 ||4 | hangzhou | wm07 | 154.67 ||5 | hangzhou | wm05 | 131.33 ||5 | hangzhou | wm06 | 154.58 ||5 | hangzhou | wm07 | 154.67 |+--------+----------+----------+--------+25 rows in set (0.00 sec)mysql>
统计每个用户的使用总量为：
mysql> SELECT Number,SUM(Wm) otal_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>
二，行变列例子演示
1，准备测试数据
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 |
+——————–+——–+——–+——–+———–+————–+
2，利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total_num
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 cnameUNION ALLSELECT"总成绩平均数",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 |
+——————–+——–+——–+——–+———–+————–+
3，利用max(CASE … WHEN … THEN .. ELSE END) AS “语文”的方式来实现
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 ALLSELECT"总成绩平均数",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 |
+——————–+——–+——–+——–+———–+————–+
4，利用 WITH rollup结果不符合
SQL代码如下：
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, otal) cource,SUM(score) AS score,ROUND(AVG(score) ,2) AS avg_scoreFROM tb GROUP BY `cname`,cource WITH ROLLUP HAVING cname IS NOT NULL)tb2 GROUP BY ame 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, otal) 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 ame 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的，但是求列的平均数有偏差，这里场景使用不是恰当。
5，使用动态SQL来实现
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 preparedmysql> 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>
参考文章地址：/uid-7692530-id-2567582.html
