按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

法一：长型数据变宽型数据

--长型数据变宽型数据
SELECT 
    s_id,
  MAX(CASE WHEN c_id='01' THEN s_score ELSE NULL END) s01,
  MAX(CASE WHEN c_id='02' THEN s_score ELSE NULL END) s02,
  MAX(CASE WHEN c_id='03' THEN s_score ELSE NULL END) s03
FROM
    score 
group by s_id

SELECT
    a.*,
  AVG(b.s_score) avg_s
FROM
   (SELECT 
      s_id,
      MAX(CASE WHEN c_id='01' THEN s_score ELSE NULL END) s01,
      MAX(CASE WHEN c_id='02' THEN s_score ELSE NULL END) s02,
      MAX(CASE WHEN c_id='03' THEN s_score ELSE NULL END) s03
   FROM
    score 
   group by s_id) a, Score b
WHERE a.s_id = b.s_id
GROUP BY b.s_id
ORDER BY avg_s DESC

法二：子查询

select a.s_id,(select s_score from score where s_id=a.s_id and c_id='01') as 语文,
                (select s_score from score where s_id=a.s_id and c_id='02') as 数学,
                (select s_score from score where s_id=a.s_id and c_id='03') as 英语,
            round(avg(s_score),2) as 平均分 from score a  GROUP BY a.s_id ORDER BY 平均分 DESC;