mysql 行专列

以备后用查找

CREATE TABLE student (
  id INT  NOT NULL auto_increment,
  `name` varchar(50) DEFAULT NULL,
  `subject` varchar(50) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  primary key (id)

);

insert into student(name,subject,score) values
('刘能' , 'chinese' , 10),
('刘能' , 'math' , 20),
('刘能' , 'english' , 30),
('赵四' , 'chinese' , 40),
('赵四' , 'math' , 50),
('赵四' , 'english' , 60),
('谢广坤' , 'chinese' , 70),
('谢广坤' , 'math' , 80),
('谢广坤' , 'english' , 90);

第1种方式： case  when

select name,
sum( case subject when 'chinese' then score else 0 end)  as 'chinese',
sum( case subject when 'math' then score else 0 end) as 'math',
sum( case subject when 'english' then score else 0 end) as 'english'
from student
group by name;

第2种方式：  if( ,  , )

select name,
 max(IF(subject = 'chinese',score,0)) as 'chinese',
 max(IF(subject = 'math',score,0)) as 'math',
 max(IF(subject = 'english',score,0)) as 'english',
 sum(score) as'total'
from student
group by name

注： 上面的两种方式中的   max（）  或  sum（）  两者针对这种场景均可互换，   if（,   ,）感觉更简洁。两者性能没有比较过。