mysql行转列查询

    

为了完成行转列的查询 直接写最终结果

select

 sum(case compared_date when '1' then oneLine else 0),

 sum(case compared_date when '2' then oneLine else 0),

 sum(case compared_date when '3' then oneLine else 0)

from 表名

        where 条件

样例：网上比较多的一个课程老师学生的例子

1. 创建表

 create table exampleTable (course varchar(20),teacher varchar(20),student varchar(20),score  int) ;

2.插入数据

insert into exampleTable (course,teacher,student,score) values ('数学',' 王华','丽丽',60);

insert into exampleTable (course,teacher,student,score) values ('数学',' 王华','李四',30);

insert into exampleTable (course,teacher,student,score) values ('语文',' 张三','丽丽',70);

insert into exampleTable (course,teacher,student,score) values ('语文',' 张三','李四',80);

3.查询结果

4.现在查询学生李四和丽丽的数学成绩和语文成绩

select student,

sum(case course when '数学' then score else 0 end) as '数学' ,

sum(case course when '语文' then score else 0 end) as '语文'

from exampleTable group by student;

查询结果