MySQL 行转列 列转行

转载：《mysql 行转列 列转行》

行转列

准备数据：

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);

查询数据表中的内容：

SELECT * FROM tb_score;

 

如果我们需要将 subject 字段的多行内容选出来，作为结果集中的不同列，并根据 userid 进行分组显示对应的score，怎么实现呢？

使用 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;

 

使用 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;

 

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

使用 SUM(IF()) 生成列 + WITH ROLLUP + 子查询生成汇总行

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;

 

使用 SUM(IF()) 生成列 + UNION 生成汇总行

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 

 

使用 SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行

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 ;

 

使用 group_concat() 合并字段显示

SELECT 
  userid,
  GROUP_CONCAT(`subject`, ":", score) AS 成绩 
FROM
  tb_score 
GROUP BY 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;

INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('001',90,92,80,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('002',88,90,75.5,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('003',70,85,90,82);

查询数据表中的内容：

SELECT * FROM tb_score1;

 

如果我们需要将 userid 的每个科目分数分散成一条记录显示出来，怎么实现呢？

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

 

这里将每个 userid 对应的多个科目的成绩查出来，通过 UNION ALL 将结果集加起来，达到上图的效果。