转载至：https://www.cnblogs.com/xiaoxi/p/7151433.html

---

 

一、行转列

　即将原本同一列下多行的不同内容作为多个字段，输出对应内容。

建表语句

 

DROP TABLE IF EXISTS tb_score;

CREATE TABLE tb_score(

    id INT(11) NOT NULL auto_increment,

    userid VARCHAR(20) NOT NULL COMMENT '用户id',

    subject VARCHAR(20) COMMENT '科目',

    score DOUBLE COMMENT '成绩',

    PRIMARY KEY(id)

)ENGINE = INNODB DEFAULT CHARSET = utf8;

插入数据

INSERT INTO tb_score(userid,subject,score) VALUES ('001','语文',90);INSERT INTO tb_score(userid,subject,score) VALUES ('001','数学',92);INSERT INTO tb_score(userid,subject,score) VALUES ('001','英语',80);INSERT INTO tb_score(userid,subject,score) VALUES ('002','语文',88);INSERT INTO tb_score(userid,subject,score) VALUES ('002','数学',90);INSERT INTO tb_score(userid,subject,score) VALUES ('002','英语',75.5);INSERT INTO tb_score(userid,subject,score) VALUES ('003','语文',70);INSERT INTO tb_score(userid,subject,score) VALUES ('003','数学',85);INSERT INTO tb_score(userid,subject,score) VALUES ('003','英语',90);INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);

 

1.使用case...when...then 进行行转列

　

SELECT userid,SUM(CASE subject WHEN '语文' THEN score ELSE 0 END) as '语文',SUM(CASE subject WHEN '数学' THEN score ELSE 0 END) as '数学',SUM(CASE subject WHEN '英语' THEN score ELSE 0 END) as '英语',SUM(CASE subject WHEN '政治' THEN score ELSE 0 END) as '政治'FROM tb_scoreGROUP BY userid

 

2.使用if()进行行转列

 

SELECT userid,sum(IF(subject='语文',score,0)) as '语文',sum(IF(subject='数学',score,0)) as '数学',sum(IF(subject='英语',score,0)) as '英语',sum(IF(subject='政治',score,0)) as '政治'FROM tb_scoreGROUP BY userid;

注意点：

　　(1) SUM()是为了能够使用GROUP BY根据userid进行分组，因为每一个userid对应的sujbect='语文'的记录只有一条，所以SUM()的值就等于对应那条记录的score的值。

　　假如userid='001' and subject='语文'的记录有两条，则此时SUM()的值将会是这两条记录的和。同理，使用Max()的值将会是者两条记录里面值最大的一个。但是正常情况下，一个user对应一个subject只有一个分数，因此可以使用SUM(),MAX(),AVG()等聚合函数都可以达到行转列的效果。

　　(2)IF(subject='语文',score,0)作为条件，即对所有subject='语文'的记录的score字段进行SUM(),MAX(),MIN(),AVG()操作，如果score没有值则默认为0.

3.利用SUM(IF())生成列 + WITH ROLLUP 生成汇总行，并利用 ifnull将汇总行标题显示为Total  　

注意：ifnull（exp1，exp2）--- 如果exp1不为null，则返回参数exp1，如果exp1为null，则返回exp2；

SELECT IFNULL(userid,'total') as userid,SUM(IF(subject='语文',score,0)) AS '语文',SUM(IF(subject='数学',score,0)) AS '数学',SUM(IF(subject='英语',score,0))AS '英语',SUM(IF(subject='政治',score,0)) AS '政治',SUM(IF(subject='total',score,0)) AS 'total'FROM(    SELECT userid,IFNULL(subject,'total') AS subject,SUM(score) AS score    FROM tb_score    GROUP BY userid,`subject`    WITH ROLLUP    HAVING userid is NOT NULL) as aGROUP BY useridWITH ROLLUP

4.利用SUM(IF())生成列，UNION生成会总行，并利用IFNULL将汇总行标题显示为total

SELECT  userid,SUM(IF(subject='语文',score,0)) AS '语文',SUM(IF(subject='数学',score,0)) AS '数学',SUM(IF(subject='英语',score,0))AS '英语',SUM(IF(subject='政治',score,0)) AS '政治',SUM(score) as 'total'FROM tb_scoreGROUP BY useridUNIONSELECT 'TOTAL',SUM(IF(subject='语文',score,0)) as 语文,SUM(IF(subject='数学',score,0)) AS 数学,SUM(IF(subject='英语',score,0)) AS 英语,SUM(IF(subject='政治',score,0)) AS 政治,SUM(score)FROM tb_score

5.利用SUM(IF())生成列，直接生成结果不再利用子查询

SELECT IFNULL(userid,'total') as userid,SUM(IF(subject='语文',score,0)) as 语文,SUM(IF(subject='数学',score,0)) as 数学,SUM(IF(subject='英语',score,0)) as 英语,SUM(IF(subject='政治',score,0)) as 政治,SUM(score) AS totalFROM tb_scoreGROUP BY useridWITH ROLLUP

6.动态，适用于列不确定情况

SET @EE='';select @EE :=CONCAT(@EE,'sum(if(subject= \'',subject,'\',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM tb_score) A ;SET @QQ = CONCAT('select ifnull(userid,\'TOTAL\')as userid,',@EE,' sum(score) as TOTAL from tb_score group by userid WITH ROLLUP');-- SELECT @QQ;PREPARE stmt FROM @QQ;EXECUTE stmt;DEALLOCATE PREPARE stmt;

7.合并字段显示：利用group_concat()

SELECT userid,GROUP_CONCAT(subject,":",score) as 成绩 from tb_scoreGROUP BY userid;

二、列转行

　　本质上将userid的每一个科目分数分散成一条条记录显示出来

建表语句

CREATE TABLE tb_score1(    id INT(11) NOT NULL auto_increment,    userid VARCHAR(20) NOT NULL COMMENT '用户id',    cn_score DOUBLE COMMENT '语文成绩',    math_score DOUBLE COMMENT '数学成绩',    en_score DOUBLE COMMENT '英语成绩',    po_score DOUBLE COMMENT '政治成绩',    PRIMARY KEY(id))ENGINE = INNODB DEFAULT CHARSET = utf8;

SQL语句

SELECT userid,'语文' as course,cn_score as score from tb_score1UNION ALLSELECT userid,'数学' as course,math_score as score from tb_score1UNION ALLSELECT userid,'英语' as course,en_score as score from tb_score1UNION ALLSELECT userid,'政治' as course,po_score as score from tb_score1ORDER BY userid

附：UNION与UNION ALL的区别(摘)：

1.对重复结果的处理：UNION会去掉重复记录，UNION ALL不会；

2.对排序的处理：UNION会排序，UNION ALL只是简单地将两个结果集合合并；

3.效率方面的区别：因为UNION会做去重和排序，因此效率比UNION ALL慢很多。