mysql 行转列

select sname,
       max(case cname
               when 'java' then (case
                   when score - (select avg(score) from subject where cname = 'java') > 20 then 'A'
                   when  score-(select avg(score) from subject where  cname = 'java') > 0  then 'B'
                   else 'D'
                   end) end) as 'java成绩',
       max(
           case cname
           when 'mysql' then (
               case
                   when score -(select avg(score) from subject where cname= 'mysql')>20 then 'A'
               when score - (select avg(score) from subject where  cname = 'mysql' )<-5 then  'B'
               else 'sb'
               end
               )
           end
           ) as 'mysql成绩',
       max(case cname
               when 'mysql' then score end)                           as 'mysql1',
       max(case cname
               when 'java' then score end)                           as 'java'

from subject
group by sname;

这是最简单的行转列例子。

创建数据库表：

create table test.subject
(
    id    int auto_increment
        primary key,
    sname varchar(500) null,
    cname varchar(500) null,
    score int          null
);

添加数据：

INSERT INTO test.subject (id, sname, cname, score) VALUES (1, 'xiaowang', 'mysql', 54);
INSERT INTO test.subject (id, sname, cname, score) VALUES (2, 'xiaowang', 'java', 1);
INSERT INTO test.subject (id, sname, cname, score) VALUES (3, 'xiaoli', 'mysql', 58);
INSERT INTO test.subject (id, sname, cname, score) VALUES (4, 'xiaoli', 'java', 54);
INSERT INTO test.subject (id, sname, cname, score) VALUES (5, 'xiaoming', 'mysql', 74);
INSERT INTO test.subject (id, sname, cname, score) VALUES (6, 'xiaoming', 'java', 100);