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

左手的ㄟ右手 2022-08-03 12:55 188阅读 0赞

**前言：**

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