执行结果正确，如下所示：

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

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

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

| 张三 | 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结果不符合

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