mysql 行数据转为列

mysql实现行转列

一开始表里面的数据如下

最后想要呈现的效果如下

sql语言如下

SELECT student_id,name,MAX(case WHEN moth = 1 then money END) as january,MAX(case WHEN moth=2 then money END) as feb,SUM(money) from student_info GROUP BY student_id,`name`

原理如下

先按月份条件逐行列出

SELECT student_id,name,case WHEN moth = 1 then money END as january,case WHEN moth=2 then money END as feb from student_info

加上 group by 关键字进行分组 和max关键字进行聚合之后

SELECT student_id,name,max(case WHEN moth = 1 then money END) as january,max(case WHEN moth=2 then money END) as feb from student_info GROUP BY student_id,`name`

     图片由

   经过max()函数取最大值(红色圆圈圈起来的值就是这组数据的对应列的最大值)之后，就变成了下面的效果