接着上一篇[https://www.cnblogs.com/yoyoketang/p/10065424.html](https://www.cnblogs.com/yoyoketang/p/10065424.html)，继续学生表SQL * 1.计算每个人的平均成绩， 要求显示字段: 学号，姓名，平均成绩 * 2.计算每个人的成绩，总分数，平均分，要求显示：学号，姓名，语文，数学，英语，总分，平均分 * 3.列出各门课程的平均成绩，要求显示字段：课程，平均成绩 * 4.列出数学成绩的排名， 要求显示字段：学号，姓名，成绩，排名 <br /> ## **万年不变学生表** 有2张表，学生表（student）基本信息如下  科目和分数表（grade）  <br /> ### **计算学生平均分数** 1.计算每个人的平均成绩， 要求显示字段: 学号，姓名，平均成绩 ``` select a.id, a.name, avg(b.score) from student a, grade b where a.id=b.id group by id ``` 或者 ~~~ select a.id, a.name, c.avg_score from student a, (select b.id, avg(b.score) as avg_score from grade b group by b.id )c where a.id = c.id ~~~  <br /> ### **统计各科目成绩** 2.计算每个人的成绩，总分数，平均分，要求显示：学号，姓名，语文，数学，英语，总分，平均分 使用case when 语法把科目字段分解成具体的科目：语文，数学， 英语 ~~~ select a.id as 学号, a.name as 姓名, (case when b.kemu='语文' then score else 0 end) as 语文, (case when b.kemu='数学' then score else 0 end) as 数学, (case when b.kemu='英语' then score else 0 end) as 英语 from student a, grade b where a.id = b.id ~~~  ~~~ SELECT a.id as 学号, a.name as 姓名, sum(case when b.kemu='语文' then score else 0 end) as 语文, sum(case when b.kemu='数学' then score else 0 end) as 数学, sum(case when b.kemu='英语' then score else 0 end) as 英语, sum(b.score) as 总分 , sum(b.score)/count(b.score) as 平均分 FROM student a, grade b where a.id = b.id GROUP BY b.id, b.id ~~~  <br /> ### **每门课程平均成绩** 3.列出各门课程的平均成绩，要求显示字段：课程，平均成绩 ~~~ select b.kemu, avg(b.score) from grade b group by b.kemu ~~~  <br /> ### **成绩排名** 4.列出数学成绩的排名， 要求显示字段：学号，姓名，成绩，排名 在查询结果表里面添加一个变量@paiming,让它自动加1 ~~~ SELECT t.id, t.score as 数学分数, @paiming := @paiming+1 as 排名 FROM (SELECT b.id, b.score FROM grade b WHERE b.kemu = '数学' ORDER BY score DESC) AS t, (SELECT @paiming := 0) r ~~~  结合student表获取学生名称 ~~~ SELECT t.id, a.name,t.score as 数学分数, @paiming := @paiming+1 as 排名 FROM (SELECT b.id, b.score FROM grade b WHERE b.kemu = '数学' ORDER BY score DESC) AS t, (SELECT @paiming := 0) r, student a WHERE a.id = t.id ~~~  <br /> ### **同结果名次相同** 上图由于同一个分数的小伙伴，排名不一样，本着公平、公正、公开的原则，同一分数名次一样 ~~~ SELECT t.id, a.name,t.score as 数学分数, (CASE WHEN @temp = t.score THEN @paiming WHEN @temp := t.score THEN @paiming :=@paiming + 1 WHEN @temp = 0 THEN @paiming :=@paiming + 1 END) AS num FROM (SELECT b.id, b.score FROM grade b WHERE b.kemu = '数学' ORDER BY score DESC) AS t, (SELECT @paiming := 0, @temp := 0) r, student a WHERE a.id = t.id ~~~  ### 排名相同的占个名次 ~~~ SELECT obj.id, obj.score as 数学, @rownum := @rownum + 1 AS num_tmp, @incrnum := (CASE WHEN @rowtotal = obj.score THEN @incrnum WHEN @rowtotal := obj.score THEN @rownum END) AS 排名 FROM (SELECT id, score FROM grade WHERE kemu = "数学" ORDER BY score DESC ) AS obj, (SELECT @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0) r ~~~