SQL 应用实例

最新推荐文章于 2025-07-12 21:22:18 发布

原创最新推荐文章于 2025-07-12 21:22:18 发布 · 463 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL

notes 专栏收录该内容

19 篇文章

订阅专栏

这篇博客探讨了如何使用SQL查询来获取学生总分排名第六到第十位的姓名及其总分，同时展示了查询各科成绩前三名及其分数的方法。

查询总分第六到十名的学生姓名以及总分

stud_crs:

select sno, sum(grade) as 总分
from stud_crs
group by sno;

select top 3 sno, sum(grade) as 总分
from stud_crs
group by sno
order by sum(grade) desc;

select top 2 * from (
	select top 3 sno, sum(grade) as 总分
	from stud_crs
	group by sno
	order by sum(grade) desc
) as TOP3
order by TOP3.总分 asc;

answer:

select * from (
	select top 2 * from (
		select top 3 sno, sum(grade) as 总分
		from stud_crs
		group by sno
		order by sum(grade) desc
	) as TOP3
	order by TOP3.总分 asc
) as TOP2ASC
order by TOP2ASC.总分 desc;

查询各科成绩的前三名以及分数

stud_crs:

select sno, cno, grade
from stud_crs
order by grade desc;

answer:

select T.sno as 学生学号, T.cno 课程编号, T.grade as 成绩
from stud_crs as T
where T.grade in (
	select top 3 grade
	from stud_crs
	where cno = T.cno
	order by grade desc
)
order by T.cno asc, T.grade desc;