一个数据库查询转换问题

有这样两个表和数据，想查询出如下结果，该怎样写sql语句呢，求各位高手帮帮小白

这个是sql行列转置.

select s.`id`,
  sum(case when j.`subject`='语文' then s.score end) as '语文',
  sum(case when j.`subject`='数学' then s.score end) as '数学',
  sum(case when j.`subject`='英语' then s.score end) as '英语',
  sum(case when j.`subject`='物理' then s.score end) as '物理',
  sum(case when j.`subject`='化学' then s.score end) as '化学',
  sum(case when j.`subject`='生物' then s.score end) as '生物',
  sum(case when j.`subject`='政治' then s.score end) as '政治',
  sum(case when j.`subject`='历史' then s.score end) as '历史',
  sum(case when j.`subject`='地理' then s.score end) as '地理'
from t_score s inner join t_subject j on s.subjectid=j.`id`