整理常见的SQL题

常见SQL题

一个学生成绩表，字段有学生姓名、班级、成绩，求各班前十名

利用子查询

select a.class, a.student_name, a.score
from test.student_scores a
where (
	select count(*)
    from test.student_scores b
    where a.class = b.class and a.score < b.score
) < 10
order by a.class, score desc;

求每个班中分数最高的学生

利用子查询：

select a.class, a.student_name, a.score
from test.student_scores a
where (
	select count(*)
    from test.student_scores b
    where a.class = b.class and a.score < b.score
) < 1
order by a.class, score desc;

也是利用子查询

select a.class, a.student_name, a.score
from test.student_scores a
where a.score = (
	select max(score)
    from test.student_scores b
    where a.class = b.class
)

利用join

select a.class, a.student_name, a.score
from test.student_scores a
inner join (
	select class, max(score) as max_score
    from test.student_scores
    group by class
) b on a.class = b.class and a.score = b.max_score

给学生表、课程成绩表，求不存在01课程但存在02课程的学生的成绩

students表，其中包含学生的sid（学生编号）和其他相关信息。
score表，其中包含sid（学生编号)，cid（课程编号）和score(分数）。

select s.name, s.score
from students s
left join score sc1 on s.sid = sc1.sid and sc1.cid = 1
left join score sc2 on s.sid = sc2.sid and sc2.cid = 2
where sc1.cid is null and sc2.cid is not null;

学生成绩表（sid,cid,score）查询总分排名在5-10名的学生id及对应的总分

rank() 是窗口函数，order by 子句在 over 子句中用于指定如何对窗口内的结果集进行排序

WITH StudentTotalScore as (
select sid, sum(socre) as totalScore
from scores
group by sid
),
StudentRank as(
select sid, totalScore,
	RANK() over (order by totalScore desc) as ranking
from StudentTotalScore
),
select sid, totalScore
from StudentRank
where ranking between 5 and 10