前言

接着上一篇https://www.cnblogs.com/yoyoketang/p/10065424.html，继续学生表SQL

万年不变学生表

有2张表，学生表（student）基本信息如下

科目和分数表（grade）

计算学生平均分数

计算每个人的平均成绩，要求显示字段: 学号，姓名，平均成绩

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

统计各科目成绩

计算每个人的成绩，总分数，平均分，要求显示：学号，姓名，语文，数学，英语，总分，平均分

使用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

每门课程平均成绩

列出各门课程的平均成绩，要求显示字段：课程，平均成绩

select b.kemu, avg(b.score)
from grade b
group by b.kemu

成绩排名

列出数学成绩的排名，要求显示字段：学号，姓名，成绩，排名

在查询结果表里面添加一个变量@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

同结果名次相同

上图由于同一个分数的小伙伴，排名不一样，本着公平、公正、公开的原则，同一分数名次一样

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

《Python3接口自动化第7期》12月8号开学

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。