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