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

wre2

前言：

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



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

这是基础数据表，里面有多个字段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，存储过程遍历：

这个存储过程建立了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))  
    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 ;  



4，列变行结果展示

临时表的所有数据：

    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>  


二，行变列例子演示
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 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 |

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


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 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 |

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


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,'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的，但是求列的平均数有偏差，这里场景使用不是恰当。


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 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>