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)+1as名次
from student t2  where course ='数学'and name ='学渣许⽼师⼉'order by score desc;
-- 统计
select name ,
sum(case WHEN course='语⽂'then score ELSE0END)  语⽂,
sum(case WHEN course='数学'then score ELSE0END) 数学,
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 between0and59then1else0end) as低于60不及格（个）,
sum(case when score between60and80then1else0end) as 60⾄80良（个）,
sum(case when score between81and100then1else0end) as 80值100优秀（个）from student
group by course;
Sqlserver
-- 查询各个学⽣的总分由⾼到低排序
select name, sum(score) as Totalscore from student group by name ORDER BY Totalscore DESC;
sewing>perl脚本与电脑设置
-- 平均分低于80分的学⽣及各科成绩
select name,sum(case WHEN course='语⽂'then score ELSE0END)  语⽂,
发那科a柜的process iosum(case WHEN course='数学'then score ELSE0END) 数学, 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 TOP1 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>80and course='语⽂'
) AND  course='语⽂'
-- 查询某⼀位学⽣的某⼀门成绩排名
select name,course,score,
(select count(*) from student t1 where course ='数学'and t1.score > t2.score)+1as名次
from student t2  where course ='数学'and name ='学渣许⽼师⼉'order by score desc;
distance吉他谱
-- 统计curl代理命令
select name ,
sum(case WHEN course='语⽂'then score ELSE0END)  语⽂,
sum(case WHEN course='数学'then score ELSE0END) 数学,
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 between0and59then1else0end) as低于60不及格（个）,
sum(case when score between60and80then1else0end) as 60⾄80良（个）,
sum(case when score between81and100then1else0end) as 80值100优秀（个）from student
group by course;
-- 统计是否及
SELECT  course as课程,
SUM(CASE WHEN score>=60THEN1ELSE0END) as及格,
SUM(CASE WHEN score>=60THEN0ELSE1END) 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" ELSE0END)  语⽂,
sum(case WHEN "course"='数学'then "score" ELSE0END) 数学,
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">80and "course"='语⽂'
) AND  "course"='语⽂'
-- 查询某⼀位学⽣的某⼀门成绩排名
select "name","course","score",(select count(*) from "student" t1 where "course" ='数学'and t1."score" > t2."score")+1as名次from "student" t2  where "course" ='数学'and "name" ='学渣许⽼师⼉'order by "score" desc;
-- 统计
select "name" ,
sum(case WHEN "course"='语⽂'then "score" ELSE0END)  语⽂,
sum(case WHEN "course"='数学'then "score" ELSE0END) 数学,
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">=60THEN1ELSE0END) as及格,
SUM(CASE WHEN "score">=60THEN0ELSE1END) as不及格
FROM  "SYSTEM"."student"
mysql语句转oracle
GROUP BY  "course";
查询语句⼤体⼀致分为三块只为⽅便查看练习使⽤未做优化如发现问题劳烦指教

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。