在SQL Server 数据库中,有一个表StudentAchievement(学生成绩),它有三个字段:StudentID(varchar(8),学生编号),CourseID(varchar(10),课程编号),Achievement(int ,成绩),写一条SQL语句,筛选出每门课程的前两名的学生编号,课程编号,成绩并排序。
select top 2 * from StudentAchievement group by CourseID order by Achievement
一条语句暂没想到,用一段语句是没问题: select CourseId,max(Achievement)as Achievement into #tmp from StudentAchievement group by CourseIdselect CourseId,max(Achievement) as Achievement into #tmp2 from StudentAchievement where Achievement not in (select Achievement from #tmp) and CourseId in (select CourseId from #tmp) group by CourseIdselect a.StudentID,a.CourseId,a.Achievement from StudentAchievement a inner join #tmp b on a.CourseId=b.CourseId and a.Achievement =b.Achievement select a.StudentID,a.CourseId,a.Achievement from StudentAchievement a inner join #tmp2 c on a.CourseId=c.CourseId and a.Achievement =c.Achievement order by a.Achievement
from StudentAchievement
group by CourseID
order by Achievement
select CourseId,max(Achievement)as Achievement
into #tmp
from StudentAchievement
group by CourseIdselect CourseId,max(Achievement) as Achievement
into #tmp2
from StudentAchievement where Achievement not in (select Achievement from #tmp)
and CourseId in (select CourseId from #tmp)
group by CourseIdselect a.StudentID,a.CourseId,a.Achievement
from StudentAchievement a
inner join #tmp b on a.CourseId=b.CourseId and a.Achievement =b.Achievement
select a.StudentID,a.CourseId,a.Achievement
from StudentAchievement a
inner join #tmp2 c on a.CourseId=c.CourseId and a.Achievement =c.Achievement
order by a.Achievement