MyQL的行转列，列转行

佚名 5年前 (2019-05-15) 随笔 2275人围观抢沙发百度已收录

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

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

一、行转列

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

建表语句

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_score
GROUP 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_score
GROUP 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 a
GROUP BY userid
WITH 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_score
GROUP BY userid
UNION
SELECT '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 total
FROM tb_score
GROUP BY userid
WITH 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_score
GROUP 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_score1
UNION ALL
SELECT userid,'数学' as course,math_score as score from tb_score1
UNION ALL
SELECT userid,'英语' as course,en_score as score from tb_score1
UNION ALL
SELECT userid,'政治' as course,po_score as score from tb_score1
ORDER BY userid