select a.student,

max(if(a.course='english',a.score,0)) as english, max(if(a.course='computer',a.score,0)) as 'computer', max(if(a.course='history',a.score,0)) as 'history', max(if(a.course='math',a.score,0)) as 'math' from scores a group by a.student;

 

SET @EE='';

SELECT  :=CONCAT(,'MAX(IF(course=\'',course,'\',score,0)) AS ',course,',') FROM (SELECT DISTINCT course FROM scores) A; select :=concat('select ',,'student from scores group by student') from dual; prepare stmt from @QQ; execute stmt;

附测试数据

create table scores(

 id int primary key,  student char(20),  course char(20),  score int );

 

insert into scores values

(1 ,'Leilei','english',9), (2 ,'Jim','english',7), (3 ,'Tom','english',4), (4 ,'John','english',2), (5 ,'Leilei','computer',2), (6 ,'Jim','computer',9), (7 ,'Tom','computer',8), (8 ,'John','computer',5), (9 ,'Leilei','history',1), (10 ,'Jim','history',8), (11 ,'Tom','history',8), (12 ,'John','history',6), (13 ,'Leilei','math',8), (14 ,'Jim','math',2), (15 ,'Tom','math',6), (16 ,'John','math',9), (17 ,'Leilei','math',3), (18 ,'Jim','math',5), (19 ,'Tom','math',2), (20 ,'John','math',5);