SQL 应用实例

最新推荐文章于 2022-08-29 07:55:36 发布

MoMo_qyc

最新推荐文章于 2022-08-29 07:55:36 发布

阅读量353

点赞数

分类专栏： notes 文章标签： SQL

本文链接：https://blog.csdn.net/MoMo_flamboyant/article/details/103619517

版权

notes 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

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

摘要由CSDN通过智能技术生成

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

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;