博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

行变列例子演示

Posted on 2016-08-08 14:42  moss_tan_jun  阅读(228)  评论(0)  编辑  收藏  举报

1，准备测试数据

[sql] view plain copy print?
  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代码块如下：

 

[sql] view plain copy print?
  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代码如下：

 

[sql] view plain copy print?
  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代码如下：
[sql] view plain copy print?
  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;  


执行结果不对，总平均数栏目，里面各科比较语文数学的班级平均数不对，如下所示：

 

[sql] view plain copy print?
  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代码块如下：

 

[sql] view plain copy print?
  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;  

 

查看执行结果如下，已经达到效果：

 

[sql] view plain copy print?
    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>