create table user_score
(
  name varchar(20),
  subjects varchar(20),
  score int
);
insert into user_score(name,subjects,score) values('張三','語文',60);
insert into user_score(name,subjects,score) values('張三','數(shù)學(xué)',70);
insert into user_score(name,subjects,score) values('張三','英語',80);
insert into user_score(name,subjects,score) values('李四','語文',90);
insert into user_score(name,subjects,score) values('李四','數(shù)學(xué)',100);

create table user_score2
(
  name varchar(20),
  yuwen int,
  shuxue int,
  yingyu int
);
insert into user_score2(name,yuwen,shuxue,yingyu) values('張三',60,70,80);
insert into user_score2(name,yuwen,shuxue,yingyu) values('李四',90,100,0);

select name,sum(case subjects when '語文' then score else 0 end) 
as '語文',sum(case subjects when '數(shù)學(xué)' then score else 0 end) 
as '數(shù)學(xué)', sum(case subjects when '英語' then score else 0 end) 
as '英語'from user_score group by name;

SELECT  name,'yuwen'  AS   subjects,yuwen  AS  score  FROM  user_score2  UNION  ALL  
SELECT  name,'shuxue'  AS   subjects,shuxue  AS  score  FROM  user_score2 UNION  ALL  
SELECT  name,'yingyu'  AS   subjects,yingyu  AS  score  FROM  user_score2 
ORDER BY name,subjects DESC;