MySQL horizontal and vertical table conversion operation implementation method

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('张三','数学',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('李四','数学',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 'Chinese' then score else 0 end) 
as 'Chinese',sum(case subjects when 'Mathematics' then score else 0 end) 
as 'Mathematics', sum(case subjects when 'English' then score else 0 end) 
as 'English'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;