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