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

行转列

准备数据：

  1. CREATE TABLE tb_score(
  2.     id INT(11) NOT NULL auto_increment,
  3.     userid VARCHAR(20) NOT NULL COMMENT '用户id',
  4.     subject VARCHAR(20) COMMENT '科目',
  5.     score DOUBLE COMMENT '成绩',
  6.     PRIMARY KEY(id)
  7. )ENGINE = INNODB DEFAULT CHARSET = utf8;
  9. INSERT INTO tb_score(userid,subject,score) VALUES ('001','语文',90);
  10. INSERT INTO tb_score(userid,subject,score) VALUES ('001','数学',92);
  11. INSERT INTO tb_score(userid,subject,score) VALUES ('001','英语',80);
  12. INSERT INTO tb_score(userid,subject,score) VALUES ('002','语文',88);
  13. INSERT INTO tb_score(userid,subject,score) VALUES ('002','数学',90);
  14. INSERT INTO tb_score(userid,subject,score) VALUES ('002','英语',75.5);
  15. INSERT INTO tb_score(userid,subject,score) VALUES ('003','语文',70);
  16. INSERT INTO tb_score(userid,subject,score) VALUES ('003','数学',85);
  17. INSERT INTO tb_score(userid,subject,score) VALUES ('003','英语',90);
  18. INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);

查询数据表中的内容：

  1. SELECT * FROM tb_score;

image.png

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

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

  1. SELECT userid,
  2.   SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) AS '语文',
  3.   SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) AS '数学',
  4.   SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) AS '英语',
  5.   SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) AS '政治' 
  6. FROM tb_score 
  7. GROUP BY userid;

image.png

使用 IF() 进行行转列

  1. SELECT userid,
  2.   SUM(IF(`subject`='语文',score,0)) AS '语文',
  3.   SUM(IF(`subject`='数学',score,0)) AS '数学',
  4.   SUM(IF(`subject`='英语',score,0)) AS '英语',
  5.   SUM(IF(`subject`='政治',score,0)) AS '政治' 
  6. FROM tb_score 
  7. GROUP BY userid;

image.png

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

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

  1. SELECT IFNULL(userid,'total') AS userid,
  2.   SUM(IF(`subject`='语文',score,0)) AS 语文,
  3.   SUM(IF(`subject`='数学',score,0)) AS 数学,
  4.   SUM(IF(`subject`='英语',score,0)) AS 英语,
  5.   SUM(IF(`subject`='政治',score,0)) AS 政治,
  6.   SUM(IF(`subject`='total',score,0)) AS total
  7. FROM(
  8.     SELECT userid,IFNULL(`subject`,'total') AS `subject`,SUM(score) AS score
  9.     FROM tb_score
  10.     GROUP BY userid,`subject`
  11.     WITH ROLLUP
  12.     HAVING userid IS NOT NULL
  13. )AS A 
  14. GROUP BY userid
  15. WITH ROLLUP;

image.png

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

  1. SELECT 
  2.   userid,
  3.   SUM(IF(`subject` = '语文', score, 0)) AS 语文,
  4.   SUM(IF(`subject` = '数学', score, 0)) AS 数学,
  5.   SUM(IF(`subject` = '英语', score, 0)) AS 英语,
  6.   SUM(IF(`subject` = '政治', score, 0)) AS 政治,
  7.   SUM(score) AS TOTAL 
  8. FROM tb_score 
  9. GROUP BY userid 
  10. UNION
  11. SELECT 
  12.   'TOTAL',
  13.   SUM(IF(`subject` = '语文', score, 0)) AS 语文,
  14.   SUM(IF(`subject` = '数学', score, 0)) AS 数学,
  15.   SUM(IF(`subject` = '英语', score, 0)) AS 英语,
  16.   SUM(IF(`subject` = '政治', score, 0)) AS 政治,
  17.   SUM(score) 
  18. FROM tb_score

image.png

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

  1. SELECT 
  2.   IFNULL(userid, 'TOTAL') AS userid,
  3.   SUM(IF(`subject` = '语文', score, 0)) AS 语文,
  4.   SUM(IF(`subject` = '数学', score, 0)) AS 数学,
  5.   SUM(IF(`subject` = '英语', score, 0)) AS 英语,
  6.   SUM(IF(`subject` = '政治', score, 0)) AS 政治,
  7.   SUM(score) AS TOTAL 
  8. FROM tb_score 
  9. GROUP BY userid WITH ROLLUP ;

image.png

使用 group_concat() 合并字段显示

  1. SELECT 
  2.   userid,
  3.   GROUP_CONCAT(`subject`, ":", score) AS 成绩 
  4. FROM
  5.   tb_score 
  6. GROUP BY userid

**
image.png

列转行

准备数据：

  1. CREATE TABLE tb_score1(
  2.     id INT(11) NOT NULL auto_increment,
  3.     userid VARCHAR(20) NOT NULL COMMENT '用户id',
  4.     cn_score DOUBLE COMMENT '语文成绩',
  5.     math_score DOUBLE COMMENT '数学成绩',
  6.     en_score DOUBLE COMMENT '英语成绩',
  7.     po_score DOUBLE COMMENT '政治成绩',
  8.     PRIMARY KEY(id)
  9. )ENGINE = INNODB DEFAULT CHARSET = utf8;
  11. INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('001',90,92,80,0);
  12. INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('002',88,90,75.5,0);
  13. INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('003',70,85,90,82);

查询数据表中的内容：

  1. SELECT * FROM tb_score1;

image.png

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

  1. SELECT userid,'语文' AS course,cn_score AS score FROM tb_score1
  2. UNION ALL
  3. SELECT userid,'数学' AS course,math_score AS score FROM tb_score1
  4. UNION ALL
  5. SELECT userid,'英语' AS course,en_score AS score FROM tb_score1
  6. UNION ALL
  7. SELECT userid,'政治' AS course,po_score AS score FROM tb_score1
  8. ORDER BY userid

image.png

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

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/ky4kgo 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。