站长资源数据库
sql查询出各科成绩最好的学生信息
简介1.相关数据表 Score表 [User]表 SQL语句如下: 复制代码 代码如下: --查询出各科成绩最好的学生信息 --自连接 --SELECT TOP 1 * FROM Score B WHERE B.ScoreName = '数学' ORDER BY B.Score DESC SELECT
1.相关数据表
Score表
[User]表
![sql查询出各科成绩最好的学生信息](//img.jbzj.com/file_images/article/201405/20140515161635.jpeg?2014415161655)
SQL语句如下:
复制代码 代码如下:
--查询出各科成绩最好的学生信息
--自连接
--SELECT TOP 1 * FROM Score B WHERE B.ScoreName = '数学' ORDER BY B.Score DESC
SELECT A.ID,U.Name,A.ScoreName,A.Score
FROM Score A,[User]U
WHERE UID IN (SELECT TOP 1 UID
FROM Score B
WHERE B.ScoreName = A.ScoreName
ORDER BY B.Score DESC) AND A.UID=U.ID
ORDER BY A.ScoreName, A.Score DESC
查询结果如下图:
![sql查询出各科成绩最好的学生信息](/UploadFiles/2021-04-09/20140515161723.jpeg?2014415161747)
Score表
![sql查询出各科成绩最好的学生信息](/UploadFiles/2021-04-09/20140515161550.jpeg?2014415161612)
[User]表
![sql查询出各科成绩最好的学生信息](http://img.jbzj.com/file_images/article/201405/20140515161635.jpeg?2014415161655)
SQL语句如下:
复制代码 代码如下:
--查询出各科成绩最好的学生信息
--自连接
--SELECT TOP 1 * FROM Score B WHERE B.ScoreName = '数学' ORDER BY B.Score DESC
SELECT A.ID,U.Name,A.ScoreName,A.Score
FROM Score A,[User]U
WHERE UID IN (SELECT TOP 1 UID
FROM Score B
WHERE B.ScoreName = A.ScoreName
ORDER BY B.Score DESC) AND A.UID=U.ID
ORDER BY A.ScoreName, A.Score DESC
查询结果如下图:
![sql查询出各科成绩最好的学生信息](/UploadFiles/2021-04-09/20140515161723.jpeg?2014415161747)