在SQL Server 数据库中,有一个表StudentAchievement(学生成绩),它有三个字段:StudentID(varchar(8),学生编号),CourseID(varchar(10),课程编号),Achievement(int ,成绩),写一条SQL语句,筛选出每门课程的前两名的学生编号,课程编号,成绩并排序。
答案:
Select * From StudentAchievement S1
Where Achievement > = ( Select Top 1 * From ( Select Top n Achievement From StudentAchievement Where CourseID =S1.CourseID Order By Achievement desc ) A1 Order By Achievement Asc )
Order By CourseID asc , Achievement desc
上面的n就是前几位!如前3位,n就是3 :)