程序博客网 > 支持向量机python代码

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

来源：互联网 发布：支持向量机python代码 编辑：程序博客网 时间：2024/05/02 07:45

 前言：

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.   
  5. INSERT INTO tb VALUES('张三','语文',74);  
  6. INSERT INTO tb VALUES('张三','数学',83);  
  7. INSERT INTO tb VALUES('张三','物理',93);  
  8. INSERT INTO tb VALUES('李四','语文',74);  
  9. INSERT INTO tb VALUES('李四','数学',84);  
  10. INSERT INTO tb VALUES('李四','物理',94);  
  11.   
  12. 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代码块如下：

  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>  
0 0
支持向量机python代码 支持向量机python代码
原创粉丝点击
热门IT博客
tomcat java使用率tomcat java使用率
下载facebook软件下载facebook软件
安全风险评估矩阵图
中医名家讲座知乎
ceic中国经济数据库
网络流行歌曲2012
java 并发处理框架
wp embed.min.js
农村淘宝合伙人举报
我想做淘宝客服
淘宝宝贝代理
光大乌龙指事件 知乎
上海网络诈骗公司名单
门诊病历软件
淘宝买鞋子调包
无极树如何实现php
泰拉瑞亚哥布林数据仪
网络印刷报价系统
ubuntu和windows
word mac 分割线
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 杨振宁夫人 杨振宁小孩 杨振宁结婚 杨振宁原配 杨振宁 翁帆 杨振宁兄弟姐妹 杨振宁老来得子 杨振宁生子 杨振宁喜得贵子 杨振宁简历 杨振宁的重孙女 杨振宁诺贝尔 杨振宁有孩子吗 杨振宁几个孩子 杨振宁近况 杨振宁是谁 杨振宁 孩子 杨振宁照片 杨振宁多少岁 杨振宁的孩子们 杨振宁几岁 杨振宁家族 杨振宁与翁帆 杨振宁有没有孩子 杨武之与杨振宁 杨振宁和 杨振宁 子女 杨振宁理论 杨振宁怎么折磨翁帆 翁帆父亲娶杨振宁孙女 杨振宁诺贝尔物理学奖 杨振宁和翁帆有孩子吗 杨振宁获得诺贝尔物理学奖 杨振宁还能干动翁帆嘛 杨振宁能满足翁帆吗 翁帆爸娶杨振宁孙女 杨振宁和翁帆的孩子 杨振宁年轻时的照片 杨振林 杨武之 杨政宁

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里