Mysql多行转多列

1,准备测试数据

USE csdn;
DROP TABLE IF EXISTS csdn.tb;
CREATE TABLE tb(`cname` VARCHAR(10),cource VARCHAR(10),score INT) ENGINE=INNODB;
   
INSERT INTO tb VALUES('张三','语文',74);
INSERT INTO tb VALUES('张三','数学',83);
INSERT INTO tb VALUES('张三','物理',93);
INSERT INTO tb VALUES('李四','语文',74);
INSERT INTO tb VALUES('李四','数学',84);
INSERT INTO tb VALUES('李四','物理',94);
   
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代码块如下:

SELECT cname AS "姓名",
    SUM(IF(cource="语文",score,0)) AS "语文",
    SUM(IF(cource="数学",score,0)) AS "数学",
    SUM(IF(cource="物理",score,0)) AS "物理",
    SUM(score) AS "总成绩",
    ROUND(AVG(score),2) AS "平均成绩"
FROM tb 
GROUP BY cname
UNION ALL
SELECT
    "总成绩平均数",
    ROUND(AVG(`语文`),2) , ROUND(AVG(`数学`),2), ROUND(AVG(`物理`),2), ROUND(AVG(`总成绩`),2), ROUND(AVG(`平均成绩`),2)
FROM(
    SELECT "all",cname AS "姓名",
        SUM(IF(cource="语文",score,0)) AS "语文",
        SUM(IF(cource="数学",score,0)) AS "数学",
        SUM(IF(cource="物理",score,0)) AS "物理",
        SUM(score) AS "总成绩",
        AVG(score) AS "平均成绩"
    FROM tb 
    GROUP BY cname
)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 |


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

3,利用max(CASE ... WHEN ... THEN .. ELSE END) AS "语文"的方式来实现

SQL代码如下:

SELECT
    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 "物理", 
    SUM(score) AS "总成绩",
    ROUND(AVG(score) ,2) AS "平均成绩"
FROM tb 
GROUP BY `cname`
UNION ALL
SELECT
    "总成绩平均数",
    ROUND(AVG(`语文`),2) , ROUND(AVG(`数学`),2), ROUND(AVG(`物理`),2), ROUND(AVG(`总成绩`),2), ROUND(AVG(`平均成绩`),2)
FROM(   SELECT 'all' ,  
        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 "物理", 
        SUM(score) AS "总成绩",
        ROUND(AVG(score) ,2) AS "平均成绩"
    FROM tb 
    GROUP BY `cname` 
)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 |


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



by 雪洁 2015-07-01 06:34:12 1937 views
我来说几句

相关文章