前言
接着上一篇,继续学生表SQL
- 1.计算每个人的平均成绩, 要求显示字段: 学号,姓名,平均成绩
- 2.计算每个人的成绩,总分数,平均分,要求显示:学号,姓名,语文,数学,英语,总分,平均分
- 3.列出各门课程的平均成绩,要求显示字段:课程,平均成绩
- 4.列出数学成绩的排名, 要求显示字段:学号,姓名,成绩,排名
万年不变学生表
有2张表,学生表(student)基本信息如下
科目和分数表(grade)
计算学生平均分数
1.计算每个人的平均成绩, 要求显示字段: 学号,姓名,平均成绩
select a.id, a.name, c.avg_score from student a,(select b.id, avg(b.score) as avg_score from grade bgroup by b.id)cwhere a.id = c.id
统计各科目成绩
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 bwhere 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 bwhere a.id = b.idGROUP BY b.id, b.id
每门课程平均成绩
3.列出各门课程的平均成绩,要求显示字段:课程,平均成绩
select b.kemu, avg(b.score)from grade bgroup by b.kemu
成绩排名
4.列出数学成绩的排名, 要求显示字段:学号,姓名,成绩,排名
在查询结果表里面添加一个变量@paiming,让它自动加1
SELECTt.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表获取学生名称
SELECTt.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 aWHERE a.id = t.id
同结果名次相同
上图由于同一个分数的小伙伴,排名不一样,本着公平、公正、公开的原则,同一分数名次一样
SELECTt.id, a.name,t.score as 数学分数, (CASEWHEN @temp = t.score THEN @paimingWHEN @temp := t.score THEN @paiming :=@paiming + 1WHEN @temp = 0 THEN @paiming :=@paiming + 1END) AS numFROM (SELECT b.id, b.score FROM grade b WHERE b.kemu = '数学' ORDER BY score DESC) AS t, (SELECT @paiming := 0, @temp := 0) r, student aWHERE a.id = t.id
排名相同的占个名次
SELECT obj.id, obj.score as 数学, @rownum := @rownum + 1 AS num_tmp, @incrnum := (CASEWHEN @rowtotal = obj.score THEN @incrnumWHEN @rowtotal := obj.score THEN @rownumEND) AS 排名FROM(SELECT id, scoreFROM gradeWHERE kemu = "数学"ORDER BYscore DESC) AS obj,(SELECT @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0) r
交流QQ群:779429633