Sql语句查询成绩大全（Mysql，sqlserver，oracle）常遇笔试题

数据结构：

Mysql

-- 查询各个学生的总分 由高到低排序
select name, sum(score) as Totalscore from student group by name ORDER BY Totalscore DESC;

-- 平均分低于80分的学生及各科成绩
select name,

sum(case WHEN course='语文' then score ELSE 0 END)  语文,
sum(case WHEN course='数学' then score ELSE 0 END) 数学,

avg(score) as Totalscore from student group by name  HAVING avg(score)<80;

-- 各科平均分
select course, avg(score) as Totalscore from student group by course;

-- 删除总成绩最高的学生 此句在mysql中不支持 请参考Sqlserver
--  delete from student where name in(SELECT  name FROM (select name, avg(score) as Totalscore from student group by name) as t ORDER BY t.Totalscore DESC limit 1);

-- 语文大于80分的 减五分 此句在mysql中不支持 请参考Sqlserver
 update student set score=score-5  
  WHERE  name (
 select name from student  where score>80 and course='语文'  
 ) AND  course='语文'

-- 某一位学生的某一门成绩排名
select name,course,score,
(select count(*) from student t1 where course ='数学' and t1.score > t2.score)+1 as 名次
 from student t2  where course ='数学' and name = '学渣许老师儿' order by score desc;

-- 统计
select name ,
sum(case WHEN course='语文' then score ELSE 0 END)  语文,
sum(case WHEN course='数学' then score ELSE 0 END) 数学,
sum(score) 总成绩,
avg(score) 平均分 from student  group by name;

-- 每门课都大于80分的学生
select distinct name from student where name not in (select distinct name from student where score<=80);

--统计
 select course 课程,sum(case when score between 0 and 59 then 1 else 0 end) as  低于60不及格（个）,
 sum(case when score between 60 and 80 then 1 else 0 end) as 60至80良（个）,
 sum(case when score between 81 and 100 then 1 else 0 end) as 80值100优秀（个） from student
 group by course;

Sqlserver

-- 查询各个学生的总分 由高到低排序
select name, sum(score) as Totalscore from student group by name ORDER BY Totalscore DESC;

-- 平均分低于80分的学生及各科成绩
select name,sum(case WHEN course='语文' then score ELSE 0 END)  语文,
sum(case WHEN course='数学' then score ELSE 0 END) 数学, avg(score) as Totalscore from student group by name  HAVING avg(score)<80;

-- 各科平均分
select course, avg(score) as Totalscore from student group by course;

-- 删除总成绩最高的学生
delete from student where name in
(SELECT TOP 1 name FROM (select name, avg(score) as Totalscore from student group by name) as t ORDER BY t.Totalscore DESC);
-- 语文大于80分的 减五分
 update student set score=score-5  
  WHERE  name in (
 select name from student  where score>80 and course='语文'  
 ) AND  course='语文'

-- 查询某一位学生的某一门成绩排名
select name,course,score,
(select count(*) from student t1 where course ='数学' and t1.score > t2.score)+1 as 名次
 from student t2  where course ='数学' and name = '学渣许老师儿' order by score desc;

-- 统计
select name ,
sum(case WHEN course='语文' then score ELSE 0 END)  语文,
sum(case WHEN course='数学' then score ELSE 0 END) 数学,
sum(score) 总成绩,
avg(score) 平均分 from student  group by name;

-- 每门课都大于80分的学生
select distinct name from student where name not in (select distinct name from student where score<=80);

--统计
 select course as 课程,sum(case when score between 0 and 59 then 1 else 0 end) as  低于60不及格（个）,
 sum(case when score between 60 and 80 then 1 else 0 end) as 60至80良（个）,
 sum(case when score between 81 and 100 then 1 else 0 end) as 80值100优秀（个） from student
 group by course;

-- 统计是否及
SELECT  course as 课程,
        SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END) as 及格,
        SUM(CASE WHEN score>=60 THEN 0 ELSE 1 END) as 不及格
FROM  student
GROUP BY  course;

Oracle

-- 查询各个学生的总分 由高到低排序
select "name",sum("score") as Totalscore from "student" group by "name" ORDER BY Totalscore DESC;

-- 平均分低于80分的学生及各科成绩
select "name", 
sum(case WHEN "course"='语文' then "score" ELSE 0 END)  语文,
sum(case WHEN "course"='数学' then "score" ELSE 0 END) 数学, 
avg("score") as average from  "student" group by "name"  HAVING avg("score")<80;

-- 各科平均分
select "course", avg("score") as  average from "student" group by "course";

-- 删除总成绩最高的学生
delete from "student" where "name" in(
SELECT "name" FROM(select "name", avg("score") as Totalscore from 
"student"  group by "name" ORDER BY Totalscore DESC) where rownum = 1
);
-- 语文大于80分的 减五分
 update "student" set "score"="score"-5  
  WHERE  "name" in (
 select "name" from "student"  where "score">80 and "course"='语文'  
 ) AND  "course"='语文'

-- 查询某一位学生的某一门成绩排名
select "name","course","score",(select count(*) from "student" t1 where "course" ='数学' and t1."score" > t2."score")+1 as 名次
 from "student" t2  where "course" ='数学' and "name" = '学渣许老师儿' order by "score" desc;

-- 统计
select "name" ,
sum(case WHEN "course"='语文' then "score" ELSE 0 END)  语文,
sum(case WHEN "course"='数学' then "score" ELSE 0 END) 数学,
sum("score") 总成绩,
avg("score") 平均分 from "student"  group by "name";

-- 每门课都大于80分的学生
select distinct "name" from "student" where "name" not in (select distinct "name" from "student" where "score"<=80);


-- 统计是否及格
SELECT  "course" as 课程,
        SUM(CASE WHEN "score">=60 THEN 1 ELSE 0 END) as 及格,
        SUM(CASE WHEN "score">=60 THEN 0 ELSE 1 END) as 不及格
FROM  "SYSTEM"."student"
GROUP BY  "course";

查询语句大体一致分为三块只为方便查看练习使用未做优化如发现问题劳烦指教

学如逆水行舟不进则退！