MySQL行转列实现和总结

一、行转列实例

1、准备数据

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

最终想要的结果是这样：

| 姓名 | 语文 | 数学 | 物理 | +------+--------+--------+----+ | 张三 | 74.00 | 83.00 | 93.00 | | 李四 | 74.00 | 84.00 | 94.00 | 

2、利用SUM(IF()) 生成列

SELECT cname AS "姓名", SUM(IF(cource="语文", score, 0)) AS "语文", SUM(IF(cource="数学", score, 0)) AS "数学", SUM(IF(cource="物理", score, 0)) AS "物理" FROM tb GROUP BY cname 

张三  74  83  93
李四  74  84  94

3，利用max(CASE … WHEN … THEN .. ELSE END) AS “语文”的方式来实现

 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 "物理" FROM tb GROUP BY `cname` 

张三  74  83  93
李四  74  84  94

4、分析

• 原有的数据是这样的：

cname cource score
张三  语文  74
张三  数学  83
张三  物理  93
李四  语文  74
李四  数学  84
李四  物理  94

• 想变成这样：

姓名    语文  数学  物理
张三  74  83  93
李四  74  84  94

暂且将原先的表称为A，之后的称为B，A想成为B，主要是讲A表中cource列中的行数据变为列，抠除行转列的语法之外，使用了GROUP BY cname进行分组，A表的业务主键是cname和cource，这是一个联合主键。

SELECT * FROM tb GROUP BY cname

张三  语文  74
李四  语文  74

• 总结一：行转列，分组(GROUP BY)的列必须是除需要行转列之外的业务主键。
  例如tb表中业务主键应该是cname和cource,但是cource需要进行行转列，所以需要按照cname分组。

这里的字段比较少，体现的不明显，可以在tb表的基础上再加一列，性别：

cname cource score gender
张三  语文  74  男
张三  数学  83  男
张三  物理  93  男
李四  语文  74  男
李四  数学  84  男
李四  物理  94  男
张三  语文  80  女
张三  数学  80  女
张三  物理  80  女

这时候业务主键是cname,cource,gender，要向进行行转列，SQL应该是这样的：

SELECT cname AS "姓名", gender 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 "物理" FROM tb GROUP BY `cname`, gender 

• 显示结果是：

张三  女   80  80  80
张三  男   74  83  93
李四  男   74  84  94

如果还是以cname分组，结果会是这样：

张三  男   80  83  93
李四  男   74  84  94