mysql行列转换_SQL 行列转换

SQL 行列转换在实际工作中应用的非常广泛，面试的时候也会经常问到这个问题，今天就来谈谈如何实现行列转换这个操作。  

1.行转列

1.1.case when

在实现行转列的过程中，最常用的函数是 case when，这个函数在之前 mysql 实现排名文章的时候也讲过(MySQL实现排名)，下面我们来讲一个老生长谈的例子，查看学生的学习成绩，首先还是来创建一个表和插入数据。

# 创建表CREATE TABLE `student` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(10)  ,`subject` varchar(10) ,`score` tinyint(3) unsigned NOT NULL,PRIMARY KEY (`id`)) ;

# 插入数据INSERT INTO `student` (id,`name`,`subject`, `score`)VALUES(1,'张三','语文',80 ),(2,'张三','数学',84 ),(3,'张三','英语',90),(4,'李四','语文',88 ),(5,'李四','数学',84 ),(6,'李四', '英语',70 ),(7,'王五','语文',90 ),(8,'王五','数学',85 ),(9,'王五','英语',60 )

# 行转列select      name,            max(case when subject='语文' then score else 0 end ) '语文',            max(case when subject='数学' then score else 0 end ) '数学',            max(case when subject='英语' then score else 0 end ) '英语'from  studentgroup by  name;

最终结果出来了，达到了我们的目的，在这里有一点需要注意一下，本文使用的是max聚合函数，sum 聚合函数效果也是一样的，为什么不能使用 min 函数呢，这是因为用 min 指定选取学生的最小值是 0。

1.2.group_cancat

使用 group_concat 可以将所有的列都放在一起。 1、功能：将 group by 产生的同一个分组中的值连接起来，返回一个字符串结果。 2、语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )

select       name ,      GROUP_CONCAT(`subject`,":",score) new_scorefrom studentgroup by name

2.列转行

使用 union 函数将相同的字段合并在一起。

# 列转行select s1.name        ,s1.subject             ,s1.scorefrom    (select           name ,          '语文'  as subject,          max(chinese) as score    from student2    group by name,subject    union     select           name ,          '数学' as subject,          max(math) as score    from student2    group by name ,subject    union     select           name ,          '英语' as subject,          max(english) as score    from student2    group by name ,subject    ) s1order by s1.name ;