[MySQL] 行列变化各种方法实现总结（行变列报表统计、列变行数据记录统计等）...

最新推荐文章于 2023-04-13 15:20:18 发布
最新推荐文章于 2023-04-13 15:20:18 发布
阅读量231 收藏
点赞数
文章标签： 数据库

 前言：

mysql行列变化，最难的就是将多个列变成多行，使用的比较多的是统计学中行变列，列变行，没有找到现成的函数或者语句，所以自己写了存储过程，使用动态sql来实现，应用业务场景，用户每个月都有使用记录数录入一张表，一个月一个字段，所以表的字段是动态增长的，现在需要实时统计当前用户使用的总数量，如果你知道有多少个字段，那么可以用select c1+c2+c3+…. From tbname where tid=’111’;来实现，但是关键是这个都是动态的，所以在应用程序端来实现确实不适宜，可以放在数据库后台在存储过程里实现。

 

而且在行变成列中，如果要写单个sql来实现，列的数目就需要写死，因为如果不知道要展示成多少列的话，就需要用动态变量，而一条sql里面无法使用动态变量。但是可以使用sql块来实现动态的效果。
  

一，列变成行例子演示

1，准备测试数据

这是基础数据表，里面有多个字段wm201403……，现在需要把N个这样的列变成行数据。

  1. USE csdn;
  2. DROP TABLE IF EXISTS flow_table;
  3. CREATE TABLE `flow_table` (
  4.   `ID` INT(11) NOT NULL AUTO_INCREMENT,
  5.   `Number` BIGINT(11) NOT NULL,
  6.   `City` VARCHAR(10) NOT NULL,
  7.   `wm201403` DECIMAL(7,2) DEFAULT NULL,
  8.   `wm201404` DECIMAL(7,2) DEFAULT NULL,
  9.   `wm201405` DECIMAL(7,2) DEFAULT NULL,
  10.   `wm201406` DECIMAL(7,2) DEFAULT NULL,
  11.   `wm201407` DECIMAL(7,2) DEFAULT NULL,
  12.   `wm201408` DECIMAL(7,2) DEFAULT NULL,
  13.   PRIMARY KEY (`ID`,`Number`)
  14. ) ENGINE=INNODB DEFAULT CHARSET=utf8;

录入一批测试数据：

  1. INSERT INTO flow_table(Number,City,wm201403,wm201404,wm201405,wm201406,wm201407,wm201408)SELECT 1,\'shanghai\',100.2,180.4,141,164,124,127;
  2. 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;
  3. 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;
  4. 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;
  5. 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，存储过程遍历：

这个存储过程建立了2张临时表，查询测试表数据形成游标，遍历游标根据主键Number来调用pro_flow_modify存储过程进行行列变化。代码如下：

  1. DELIMITER $$
  2. DROP PROCEDURE IF EXISTS csdn.`proc_all_changes`$$
  3. CREATE PROCEDURE csdn.proc_all_changes()
  4. BEGIN
  5.     DECLARE v_number BIGINT;
  6.     DECLARE v_city VARCHAR(10);
  7.     DECLARE _done INT DEFAULT 0;
  8.    
  9.     /*定义游标*/
  10.     DECLARE cur_all CURSOR FOR SELECT Number,City FROM csdn.`flow_table`;
  11.     /**这里如果需要定义下当NOT FOUND的时候，EXIT退出游标遍历，不然如果设置成CONTINUE会一直执行下去。*/
  12.     DECLARE EXIT HANDLER FOR NOT FOUND BEGIN SET _done=1;END;
  13.    
  14.        /*建立临时表，存放所有字段的临时表*/
  15.          DROP TABLE IF EXISTS flow_n_columns;
  16.          CREATE TABLE `flow_n_columns` (
  17.            `column_name` VARCHAR(10) NOT NULL
  18.          ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  19.  
  20.          /*存放最终变成行的数据表*/
  21.          DROP TABLE IF EXISTS flow_tmp;
  22.          CREATE TABLE `flow_tmp` (
  23.            `Number` INT(11) DEFAULT NULL,
  24.            `City` VARCHAR(10) DEFAULT NULL,
  25.            `wm_str` VARCHAR(10) DEFAULT NULL,
  26.            `Wm` DECIMAL(7,2) DEFAULT NULL
  27.          ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  28.  
  29.     OPEN cur_all;
  30.     REPEAT
  31.         FETCH cur_all INTO v_number, v_city;
  32.         IF NOT _done THEN
  33.                    CALL csdn.pro_flow_modify(v_number,v_city);
  34.         END IF;
  35.         UNTIL _done=1 END REPEAT;
  36.     CLOSE cur_all;
  37.              /*展示下所有的行转列的数据**/
  38.          SELECT * FROM csdn.flow_tmp;
  39. END$$
  40. DELIMITER ;


3，行里变化存储过程

通过查询系统表information_schema.`COLUMNS`来获取测试表flow_table的所有列，然后写动态SQL，来把列的值录入到临时表flow_tmp中。

  1. DELIMITER $$
  2. DROP PROCEDURE IF EXISTS csdn.`pro_flow_modify`$$
  3. CREATE PROCEDURE csdn.`pro_flow_modify`(p_Number INT,p_city VARCHAR(10))
  4. BEGIN
  5.          DECLARE v_column_name VARCHAR(10) DEFAULT \'\';
  6.          DECLARE v_exe_sql VARCHAR(1000) DEFAULT \'\';
  7.          DECLARE v_start_wm VARCHAR(10) DEFAULT \'\';
  8.          DECLARE v_end_wm VARCHAR(10) DEFAULT \'\';
  9.          DECLARE v_num DECIMAL(10,2) DEFAULT 0;
  10.         
  11.          DECLARE i INT DEFAULT 1;
  12.          DECLARE v_Number INT DEFAULT 0;
  13.          SET v_Number=p_Number;
  14.         
  15.          DELETE FROM csdn.flow_n_columns;
  16.          DELETE FROM csdn.flow_tmp WHERE Number=v_Number;
  17.         
  18.         
  19.          /*把测试表flow_table的所有字段都录入字段临时表中，这样就达到了从列变成行的目的*/
  20.          INSERT INTO flow_n_columns
  21.          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\');
  22.          SELECT column_name INTO v_column_name FROM csdn.flow_n_columns LIMIT 1;
  23.         
  24.          /*开始循环遍历字段临时表的字段数据，并且把字段值放入临时表flow_tmp里面*/
  25.          WHILE i>0 DO
  26.                    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,\';\');
  27.                    SET @sql=v_exe_sql;
  28.                    PREPARE s1 FROM @sql;
  29.                    EXECUTE s1;
  30.                    DEALLOCATE PREPARE s1;
  31.                    DELETE FROM csdn.flow_n_columns WHERE column_name=v_column_name;
  32.                    SELECT column_name INTO v_column_name FROM csdn.flow_n_columns LIMIT 1;
  33.                    SELECT COUNT(1) INTO i FROM csdn.flow_n_columns ;
  34.                    DELETE FROM csdn.flow_tmp WHERE Wm=0;
  35.          END WHILE;
  36.  
  37.          /*由于触发器是不支持动态sql，所以不能使用while循环，动态遍历所有统计列的，只能写死列了，如下所示：
  38.          现在一个个insert只能写死了, flow_table表有多少个统计列就写多少个insert sql，以后新添加一个列，就在这里新添加一条insertsql语句
  39.          INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,\'wm201403\',wm201403 FROM flow_table WHERE Number=v_Number ;
  40.          INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,\'wm201404\',wm201404 FROM flow_table WHERE Number=v_Number ;
  41.          INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,\'wm201405\',wm201405 FROM flow_table WHERE Number=v_Number ;
  42.          INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,\'wm201406\',wm201406 FROM flow_table WHERE Number=v_Number ;
  43.          INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,\'wm201407\',wm201407 FROM flow_table WHERE Number=v_Number ;
  44.          INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,\'wm201408\',wm201408 FROM flow_table WHERE Number=v_Number ;
  45.          */
  46.         
  47.          /*清除掉不数据=0的列*/
  48.          DELETE FROM csdn.flow_tmp WHERE Wm=0 OR Wm IS NULL;
  49.         
  50.          SELECT wm_str INTO v_start_wm FROM csdn.flow_tmp WHERE Number=v_Number ORDER BY wm_str ASC LIMIT 1;
  51.          SELECT wm_str INTO v_end_wm FROM csdn.flow_tmp WHERE Number=v_Number ORDER BY wm_str DESC LIMIT 1;
  52.          SELECT SUM(Wm) INTO v_num FROM csdn.flow_tmp WHERE Number=v_Number;
  53.         
  54.  
  55.     END$$
  56.  
  57. DELIMITER ;

4，列变行结果展示

临时表的所有数据：

  1. mysql> SELECT * FROM csdn.flow_tmp;
  2. +--------+----------+----------+--------+
  3. | Number | City | wm_str | Wm |
  4. +--------+----------+----------+--------+
  5. | 1 | shanghai | wm201403 | 100.20 |
  6. | 1 | shanghai | wm201404 | 180.40 |
  7. | 1 | shanghai | wm201405 | 141.00 |
  8. | 1 | shanghai | wm201406 | 164.00 |
  9. | 1 | shanghai | wm201407 | 124.00 |
  10. | 1 | shanghai | wm201408 | 127.00 |
  11. | 2 | shanghai | wm201403 | 110.23 |
  12. | 2 | shanghai | wm201404 | 180.34 |
  13. | 2 | shanghai | wm201405 | 141.23 |
  14. | 2 | shanghai | wm201406 | 104.78 |
  15. | 2 | shanghai | wm201407 | 124.67 |
  16. | 2 | shanghai | wm201408 | 127.45 |
  17. | 3 | beijing | wm201403 | 123.23 |
  18. | 3 | beijing | wm201404 | 110.34 |
  19. | 3 | beijing | wm201405 | 131.33 |
  20. | 3 | beijing | wm201406 | 154.58 |
  21. | 3 | beijing | wm201407 | 154.67 |
  22. | 3 | beijing | wm201408 | 167.45 |
  23. | 4 | hangzhou | wm201404 | 110.34 |
  24. | 4 | hangzhou | wm201405 | 131.33 |
  25. | 4 | hangzhou | wm201406 | 154.58 |
  26. | 4 | hangzhou | wm201407 | 154.67 |
  27. | 5 | hangzhou | wm201405 | 131.33 |
  28. | 5 | hangzhou | wm201406 | 154.58 |
  29. | 5 | hangzhou | wm201407 | 154.67 |
  30. +--------+----------+----------+--------+
  31. 25 rows in set (0.00 sec)
  32. mysql>

统计每个用户的使用总量为：

  1. mysql> SELECT Number,SUM(Wm) \'total_num\' FROM flow_tmp GROUP BY Number ORDER BY Number;
  2. +--------+-----------+
  3. | Number | total_num |
  4. +--------+-----------+
  5. | 1 | 836.60 |
  6. | 2 | 788.70 |
  7. | 3 | 841.60 |
  8. | 4 | 550.92 |
  9. | 5 | 440.58 |
  10. +--------+-----------+
  11. 5 rows in set (0.00 sec)
  12.  
  13. mysql>
 

二，行变列例子演示

1，准备测试数据

  1. USE csdn;
  2. DROP TABLE IF EXISTS csdn.tb;
  3. CREATE TABLE tb(`cname` VARCHAR(10),cource VARCHAR(10),score INT) ENGINE=INNODB;
  4. INSERT INTO tb VALUES(\'张三\',\'语文\',74);
  5. INSERT INTO tb VALUES(\'张三\',\'数学\',83);
  6. INSERT INTO tb VALUES(\'张三\',\'物理\',93);
  7. INSERT INTO tb VALUES(\'李四\',\'语文\',74);
  8. INSERT INTO tb VALUES(\'李四\',\'数学\',84);
  9. INSERT INTO tb VALUES(\'李四\',\'物理\',94);

需要得到的结果是：

+--------------------+--------+--------+--------+-----------+--------------+

| 姓名               | 语文   | 数学   | 物理   | 总成绩    | 平均成绩     |

+--------------------+--------+--------+--------+-----------+--------------+

| 张三               |  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代码块如下：

  1. SELECT cname AS \"姓名\",
  2.          SUM(IF(cource=\"语文\",score,0)) AS \"语文\",
  3.          SUM(IF(cource=\"数学\",score,0)) AS \"数学\",
  4.          SUM(IF(cource=\"物理\",score,0)) AS \"物理\",
  5.          SUM(score) AS \"总成绩\",
  6.          ROUND(AVG(score),2) AS \"平均成绩\"
  7. FROM tb
  8. GROUP BY cname
  9. UNION ALL
  10. SELECT
  11.          \"总成绩平均数\",
  12.          ROUND(AVG(`语文`),2) , ROUND(AVG(`数学`),2), ROUND(AVG(`物理`),2), ROUND(AVG(`总成绩`),2), ROUND(AVG(`平均成绩`),2)
  13. FROM(
  14.          SELECT \"all\",cname AS \"姓名\",
  15.                    SUM(IF(cource=\"语文\",score,0)) AS \"语文\",
  16.                    SUM(IF(cource=\"数学\",score,0)) AS \"数学\",
  17.                    SUM(IF(cource=\"物理\",score,0)) AS \"物理\",
  18.                    SUM(score) AS \"总成绩\",
  19.                    AVG(score) AS \"平均成绩\"
  20.          FROM tb
  21.          GROUP BY cname
  22. )tb2
  23. 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代码如下：

  1. SELECT
  2.          cname AS \"姓名\",
  3.          MAX(CASE cource WHEN \"语文\" THEN score ELSE 0 END) AS \"语文\",
  4.          MAX(CASE cource WHEN \"数学\" THEN score ELSE 0 END) AS \"数学\",
  5.          MAX(CASE cource WHEN \"物理\" THEN score ELSE 0 END) AS \"物理\",
  6.          SUM(score) AS \"总成绩\",
  7.          ROUND(AVG(score) ,2) AS \"平均成绩\"
  8. FROM tb
  9. GROUP BY `cname`
  10. UNION ALL
  11. SELECT
  12.          \"总成绩平均数\",
  13.          ROUND(AVG(`语文`),2) , ROUND(AVG(`数学`),2), ROUND(AVG(`物理`),2), ROUND(AVG(`总成绩`),2), ROUND(AVG(`平均成绩`),2)
  14. FROM( SELECT \'all\' ,
  15.                    cname AS \"姓名\",
  16.                    MAX(CASE cource WHEN \"语文\" THEN score ELSE 0 END) AS \"语文\",
  17.                    MAX(CASE cource WHEN \"数学\" THEN score ELSE 0 END) AS \"数学\",
  18.                    MAX(CASE cource WHEN \"物理\" THEN score ELSE 0 END) AS \"物理\",
  19.                    SUM(score) AS \"总成绩\",
  20.                    ROUND(AVG(score) ,2) AS \"平均成绩\"
  21.          FROM tb
  22.          GROUP BY `cname`
  23. )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代码如下：


  1. SELECT IFNULL(cname,\'总平均数\') AS \"姓名\",
  2.          MAX(CASE cource WHEN \"语文\" THEN score ELSE 0 END) AS \"语文\",
  3.          MAX(CASE cource WHEN \"数学\" THEN score ELSE 0 END) AS \"数学\",
  4.          MAX(CASE cource WHEN \"物理\" THEN score ELSE 0 END) AS \"物理\",
  5.          ROUND(AVG(score),2) AS \"总成绩\",
  6.          ROUND(AVG(avg_score),2) AS \"平均成绩\"
  7.     FROM(
  8.          SELECT
  9.                    cname ,
  10.                    IFNULL(cource,\'total\') cource,
  11.                    SUM(score) AS score,
  12.                    ROUND(AVG(score) ,2) AS avg_score
  13.          FROM tb
  14.          GROUP BY `cname`,cource WITH ROLLUP HAVING cname IS NOT NULL
  15.          )tb2
  16.          GROUP BY tb2.cname WITH ROLLUP;


执行结果不对，总平均数栏目，里面各科比较语文数学的班级平均数不对，如下所示：

  1. mysql> SELECT IFNULL(cname,\'总平均数\') AS \"姓名\",
  2.     -> MAX(CASE cource WHEN \"语文\" THEN score ELSE 0 END) AS \"语文\",
  3.     -> MAX(CASE cource WHEN \"数学\" THEN score ELSE 0 END) AS \"数学\",
  4.     -> MAX(CASE cource WHEN \"物理\" THEN score ELSE 0 END) AS \"物理\",
  5.     -> ROUND(AVG(score),2) AS \"总成绩\",
  6.     -> ROUND(AVG(avg_score),2) AS \"平均成绩\"
  7.     -> FROM(
  8.     -> SELECT
  9.     ->
  10. Display ALL 793 possibilities? (Y OR n)
  11.     -> cname ,
  12.     ->
  13. Display ALL 793 possibilities? (Y OR n)
  14.     -> IFNULL(cource,\'total\') cource,
  15.     ->
  16. Display ALL 793 possibilities? (Y OR n)
  17.     -> SUM(score) AS score,
  18.     ->
  19. Display ALL 793 possibilities? (Y OR n)
  20.     -> ROUND(AVG(score) ,2) AS avg_score
  21.     -> FROM tb
  22.     -> GROUP BY `cname`,cource WITH ROLLUP HAVING cname IS NOT NULL
  23.     -> )tb2
  24.     -> GROUP BY tb2.cname WITH ROLLUP;
  25. +--------------+--------+--------+--------+-----------+--------------+
  26. | 姓名 | 语文 | 数学 | 物理 | 总成绩 | 平均成绩 |
  27. +--------------+--------+--------+--------+-----------+--------------+
  28. | 张三 | 74 | 83 | 93 | 125.00 | 83.33 |
  29. | 李四 | 74 | 84 | 94 | 126.00 | 84.00 |
  30. | 总平均数 | 74 | 84 | 94 | 125.50 | 83.67 |
  31. +--------------+--------+--------+--------+-----------+--------------+
  32. 3 ROWS IN SET, 1 warning (0.00 sec)
  33.  
  34. mysql>

总结： WITH rollup中对求列的总数是OK的，但是求列的平均数有偏差，这里场景使用不是恰当。


5，使用动态SQL来实现

SQL代码块如下：

  1. /*仅仅班级成员部分*/
  2. SET @a=\'\';
  3. SELECT @a:=CONCAT(@a,\'SUM(IF(cource=\\\'\',cource,\'\\\'\',\',score,0)) AS \',cource,\',\') FROM (SELECT DISTINCT cource FROM tb) A;
  4. SET @a=CONCAT(@a,\"ROUND(AVG(score) ,2) AS \\\"平均成绩\\\"\");
  5. SET @b=CONCAT(\'SELECT IFNULL(cname,\\\'总成绩\\\'),\',LEFT(@a,LENGTH(@a)-1),\' ,SUM(score) AS \\\"总成绩\\\" FROM tb GROUP BY cname \');
  6.  
  7. /*班级成员总计部分**/
  8. SET @a2=\"\";
  9. SET @b2=CONCAT(\'SELECT \"all\",IFNULL(cname,\\\'总成绩\\\'),\',LEFT(@a,LENGTH(@a)-1),\' ,SUM(score) AS \\\"总成绩\\\" FROM tb GROUP BY cname \');
  10. SELECT @a2:=CONCAT(@a2,\'ROUND(AVG(`\',cource,\'`),2),\') FROM (SELECT DISTINCT cource FROM tb) A;
  11. SET @a2=CONCAT(@a2,\" ROUND(AVG(`平均成绩`),2),ROUND(AVG(`总成绩`),2) \");
  12. SET @c=CONCAT(\"SELECT \\\"班级平均数\\\",\",LEFT(@a2,LENGTH(@a)-1),\" FROM(\",@b2,\")tb2 GROUP BY tb2.all;\");
  13. SET @d=CONCAT(@b,\" UNION ALL \",@c);
  14.  
  15. PREPARE stmt1 FROM @d;
  16. EXECUTE stmt1;

查看执行结果如下，已经达到效果：

  1. mysql> /*仅仅班级成员部分*/
  2. mysql> SET @a=\'\';
  3. QUERY OK, 0 ROWS affected (0.00 sec)
  4.  
  5. mysql> SELECT @a:=CONCAT(@a,\'SUM(IF(cource=\\\'\',cource,\'\\\'\',\',score,0)) AS \',cource,\',\') FROM (SELECT DISTINCT cource FROM tb) A;
  6. +-----------------------------------------------------------------------------------------------------------------------------------+
  7. | @a:=CONCAT(@a,\'SUM(IF(cource=\\\'\',cource,\'\\\'\',\',score,0)) AS \',cource,\',\') |
  8. +-----------------------------------------------------------------------------------------------------------------------------------+
  9. | SUM(IF(cource=\'语文\',score,0)) AS 语文, |
  10. | SUM(IF(cource=\'语文\',score,0)) AS 语文,SUM(IF(cource=\'数学\',score,0)) AS 数学, |
  11. | SUM(IF(cource=\'语文\',score,0)) AS 语文,SUM(IF(cource=\'数学\',score,0)) AS 数学,SUM(IF(cource=\'物理\',score,0)) AS 物理, |
  12. +-----------------------------------------------------------------------------------------------------------------------------------+
  13. 3 ROWS IN SET (0.00 sec)
  14.  
  15. mysql> SET @a=CONCAT(@a,\"ROUND(AVG(score) ,2) AS \\\"平均成绩\\\"\");
  16. QUERY OK, 0 ROWS affected (0.00 sec)
  17.  
  18. mysql> SET @b=CONCAT(\'SELECT IFNULL(cname,\\\'总成绩\\\'),\',LEFT(@a,LENGTH(@a)-1),\' ,SUM(score) AS \\\"总成绩\\\" FROM tb GROUP BY cname \');
  19. QUERY OK, 0 ROWS affected (0.00 sec)
  20.  
  21. mysql>
  22. mysql> /*班级成员总计部分**/
  23. mysql> SET @a2=\"\";
  24. QUERY OK, 0 ROWS affected (0.00 sec)
  25.  
  26. mysql> SET @b2=CONCAT(\'SELECT \"all\",IFNULL(cname,\\\'总成绩\\\'),\',LEFT(@a,LENGTH(@a)-1),\' ,SUM(score) AS \\\"总成绩\\\" FROM tb GROUP BY cname \');
  27. QUERY OK, 0 ROWS affected (0.00 sec)
  28.  
  29. mysql> SELECT @a2:=CONCAT(@a2,\'ROUND(AVG(`\',cource,\'`),2),\') FROM (SELECT DISTINCT cource FROM tb) A;
  30. +-----------------------------------------------------------------------+
  31. | @a2:=CONCAT(@a2,\'ROUND(AVG(`\',cource,\'`),2),\') |
  32. +-----------------------------------------------------------------------+
  33. | ROUND(AVG(`语文`),2), |
  34. | ROUND(AVG(`语文`),2),ROUND(AVG(`数学`),2), |
  35. | ROUND(AVG(`语文`),2),ROUND(AVG(`数学`),2),ROUND(AVG(`物理`),2), |
  36. +-----------------------------------------------------------------------+
  37. 3 ROWS IN SET (0.00 sec)
  38.  
  39. mysql> SET @a2=CONCAT(@a2,\" ROUND(AVG(`平均成绩`),2),ROUND(AVG(`总成绩`),2) \");
  40. QUERY OK, 0 ROWS affected (0.00 sec)
  41.  
  42. mysql> SET @c=CONCAT(\"SELECT \\\"班级平均数\\\",\",LEFT(@a2,LENGTH(@a)-1),\" FROM(\",@b2,\")tb2 GROUP BY tb2.all;\");
  43. QUERY OK, 0 ROWS affected (0.00 sec)
  44.  
  45. mysql> SET @d=CONCAT(@b,\" UNION ALL \",@c);
  46. QUERY OK, 0 ROWS affected (0.00 sec)
  47.  
  48. mysql>
  49. mysql> PREPARE stmt1 FROM @d;
  50. QUERY OK, 0 ROWS affected (0.00 sec)
  51. Statement prepared
  52.  
  53. mysql> EXECUTE stmt1;
  54. +---------------------------+--------+--------+--------+--------------+-----------+
  55. | IFNULL(cname,\'总成绩\') | 语文 | 数学 | 物理 | 平均成绩 | 总成绩 |
  56. +---------------------------+--------+--------+--------+--------------+-----------+
  57. | 张三 | 74.00 | 83.00 | 93.00 | 83.33 | 250.00 |
  58. | 李四 | 74.00 | 84.00 | 94.00 | 84.00 | 252.00 |
  59. | 班级平均数 | 74.00 | 83.50 | 93.50 | 83.67 | 251.00 |
  60. +---------------------------+--------+--------+--------+--------------+-----------+
  61. 3 ROWS IN SET (0.00 sec)
  62.  
  63. mysql>

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26230597/viewspace-1266684/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26230597/viewspace-1266684/

citan9616
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
sql事务详解＆数据库日志文件＆一行数据及其更新过程
6
07-26 488
sql事务详解＆数据库日志文件＆一行数据及其更新过程一、事务1.事务的概念2.事务四大特性（ACID）①原子性（Atomicity）②一致性（Consistency）③隔离性（Isolation）④持久性（Durability）3.事务的隔离级别①读未提交Read Uncommitted②读已提交Read Committed③可重复读Repetable Read④串行Serializable4.设置mysql的隔离级别5.并发事务导致的问题①丢失更新②脏读③幻读（虚读）④不可重复读重要二、数据库中的一行1.
mysql 合计_mysql-查询不同列的数量合计
weixin_42508792的博客
01-18 417
车辆违规信息表testmodel_test表结构：表字段：cra_id(车牌号)，if_weigui(该次行驶是否违规，0是正常，1是违规)目的：查询表中共有几辆车，违规的有几辆车；方法1SELECT COUNT(f.cra_id) carNum, SUM(f.weigui) weiguiNum FROM(SELECT a.cra_id,b.weiguiFROM testmodel_test aL...
mysql报表展示需求转化:实现行转列,同时确定每条记录
mtj66的博客，交流WX:SpringBreeze1104
03-15 339
原始需求:将一张二维表,展示在 css中,无奈前端展示部分数据处理功能太弱,只能重新考虑需求以及实现方式. 如下是最初的实现方式,但是和其他人员沟通之后说是不支持,(⊙o⊙)…,沟通也是生产力啊,下面的白费了. select a.accident_month ,case when b.monthId=0 and a.progress_month=b.accident_month the...
报表 动态列（mysql 行转列）
weixin_30950607的博客
08-08 261
1.平常数据库表明细都是存的明细，比如这种： 需要转换成这种： 1.sql 查询如下： SELECT student_name, GROUP_CONCAT(CONCAT(class_name,'_',score)) AS extInfo FROM t_student WHERE yn=1 and <其他条件>...
case的用法（可以把数据库里的列以行的方式显示出来！）
java_lbvs的专栏
02-01 1164
今天在做报表的时候遇到了一个数据排版的问题不知道怎么搞，请教了我们的头，给我说case可以解决，又到网上查了下其用法终于搞定了！所以当做笔记记下！呵呵！  将数据库中的列值以行值形式输出的办法：解决方案是：利用sql的语法（case语句搞定！） case有两种语法结构： 1.将某个表达式与一组简单的值进行比较以确定返回结果。  CASE input_e
将SQL数据表的一列转换为一行.docx
06-19
在 MySQL 中，可以使用以下方法来将 SQL 数据表的一列转换为一行。 知识点1：使用存储过程实现数据转换 在 MySQL 中，可以使用存储过程来实现数据转换。存储过程是一种预先编译好的 SQL 语句集合，可以重复使用以...
利用MySQL统计一列中不同值的数量方法示例
09-09
在MySQL数据库中，统计一列中不同值的数量是一项常见的数据查询任务，特别是在数据分析和报表生成时。本篇文章将深入探讨几种不同的方法来实现这一目标，这些方法适用于多种情况，无论是一小部分特定值的计数还是对...
mysql按照天统计报表当天没有数据填0的实现代码
09-09
主要介绍了mysql按照天统计报表当天没有数据填0的实现方法,需要的朋友可以参考下
mysql行转列（将同一列下的不同内容的几行数据，转换成几列显示）、列转行、行列汇总、合并显示
09-03
MySQL 行转列、列转行、行列汇总、合并显示 MySQL 行转列是一种常用的数据处理操作，用于将同一列下的不同内容的几行数据转换成几列显示。例如，我们有一个成绩表 tb_score，其中包含 userid、subject 和 score 三...
pandas.DataFrame的pivot()和unstack()实现行转列
12-25
示例：有如下表需要进行行转列： 代码如下： # -*- coding:utf-8 -*- import pandas as pd import MySQLdb from warnings import filterwarnings # 由于create table if not exists总会抛出warning，因此使用...
Mysql中行转列算法
12-24
mysql行转列算法举例与分析，有益于对mysql语法学习
HIVE多行转多列 和 多列转多行
01-07
1、HIVE多行转多列 源数据样式 把CAMERA_NO，RESULT_DATA两列转换为CAMERA_NO字段的数据为列名，RESULT_DATA字段对应CAMERA_NO的数据为值 方法一：利用str_to_map函数 alter table ods.iot.iot_5060_iotdaq.5060_aac_mtf_meas_results_new add if not exists partition(date='{DATE}' ,hour='{HOUR}',minutes='{MINUTES}'); insert overwrite table ods.iot.iot_5060_io
sql由竖向的数据转变成横向的数据.txt
11-20
Sql在查询操作时，根据年、月等条件查询出的某一物品的销售量数据时，数据显示为树形，在使用sql后，将树形的数据，转化为横向展示，即年、月作为列显示
MySQL 行转列 -》动态行转列 -》动态行转列带计算
weixin_30294709的博客
05-25 236
Pivot Table Using MySQL - A Complete Guide | WebDevZoomhttp://webdevzoom.com/pivot-table-using-mysql/ Cross-Tabulation (Pivot Tables) with MySQL - CodeProjecthttps://www.codeproject.com/articles/3633...
全国计算机报告考点,全国计算机等级考试二级Access考点分析之报表(2)
weixin_34493203的博客
07-14 504
考点6 表格式报表表格式报表是以整齐的行、列形式显示记录数据，通常一行显示一条记录、一页显示多行记录。表格式报表与纵栏式报表不同，其记录数据的字段标题信息不是被安排在每页的主体节区内显示，而是安排在页面页眉节区内显示。考点7 图表报表图表报表是指包含图表显示的报表类型。报表中使用图表，可以更直观地表示数据之间的关系。考点8 标签报表标签报表是一种特殊类型的报表在实际应用中，经常使用标签，可以通过标...
70.日常问题整理[2023/04/06]MySql 动态多列转多行
weixin_46876034的博客
04-06 546
假如userid =‘001’ and subject=‘语文’ 的记录有两条，则此时SUM() 的值将会是这两条记录的和，同理，使用Max()的值将会是这两条记录里面值最大的一个。通俗点理解，其实是这样的：group_concat()会计算哪些行属于同一组，将属于同一组的列显示出来。（2）IF(subject=‘语文’,score,0) 作为条件，即对所有subject='语文’的记录的score字段进行SUM()、MAX()、MIN()、AVG()操作，如果score没有值则默认为0。
mysql多行转多列，多列转多行
feisanking的博客
09-06 2703
多行转多列，多列转多行
mysql 列转成多行
chengchong_cc的博客
04-13 369
【代码】mysql 列转成多行。
【MySQL】窗口函数 + 行转列 + 列转行
热门推荐
weixin_44623551的博客
08-27 1万+
【MySQL】窗口函数 + 行转列 + 列转行
mysql 行转列的实现方法
最新发布
05-15
MySQL 行转列的实现方法可以使用 GROUP_CONCAT 函数，该函数将多行数据连接成一行。具体步骤如下： 1. 使用 GROUP BY 将数据分组； 2. 在 SELECT 语句中使用 GROUP_CONCAT 函数将分组后的数据连接成一行； 3. 使用...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值