select *
from StudentAchievement a
where (select count(*) from (select CourseID, Achievement from StudentAchievement group by CourseID, Achievement ) b where b.CourseID=a.CourseID and b.Achievement>=a.Achievement)<3
from StudentAchievement a
where (select count(*) from (select CourseID, Achievement from StudentAchievement group by CourseID, Achievement ) b where b.CourseID=a.CourseID and b.Achievement>=a.Achievement)<3
Where Not Exists(Select 1 from StudentAchievement Where CourseID=A.CourseID And Achievement>A.Achievement Having Count(*)>1)
Order by CourseID,Achievement Desc
Create table StudentAchievement
(CourseID Varchar(10),
StudentID Varchar(8),
Achievement Int)
--插入数据
Insert StudentAchievement Values('3-245', '103', 86)
Insert StudentAchievement Values('3-245', '105', 75)
Insert StudentAchievement Values('3-245', '109', 68)
Insert StudentAchievement Values('3-105', '103', 92)
Insert StudentAchievement Values('3-105', '105', 88)
Insert StudentAchievement Values('3-105', '109', 76)
Insert StudentAchievement Values('3-105', '101', 64)
Insert StudentAchievement Values('3-105', '108', 78)
Insert StudentAchievement Values('6-166', '101', 85)
Insert StudentAchievement Values('6-166', '107', 79)
Insert StudentAchievement Values('6-166', '108', 81)
--测试
Select * from StudentAchievement A
Where Not Exists(Select 1 from StudentAchievement Where CourseID=A.CourseID And Achievement>A.Achievement Having Count(*)>1)
Order by CourseID,Achievement Desc
--删除测试环境
Drop Table StudentAchievement
--结果
/*
CourseID StudentID Achievement
3-105 103 92
3-105 105 88
3-245 103 86
3-245 105 75
6-166 101 85
6-166 108 81
*/
第一名的数据在数据库中没有比当前Achievement高的记录。
第而名的数据在数据库中比当前Achievement高的记录只有一条,
其余的就是满足条件的记录。那段语句就是在数据库中查询,看有没有和当前记录的 CourseID相同,但是 Achievement比当前记录大的,如果满足条件的条数大于1,就是不满足条件的。剔除那些不满足条件的,剩下的就是满足条件的了。