求一SQL语句：取每个班前三名

[sc_arhat]

成绩表有三个字段：学号、班级、成绩，如何将每个班的前三名的信息取出来？

[grjs2004]

select top 3 * from 成绩表
group by 班级

[grjs2004]

select top 3 * from 成绩表
order by 成绩
group by 班级

[BES]

有这么简单么？！ 有10个人并列排名（分数一样）呢

[liangpei2008]

简单！
Declare @t table(id int,StudentNum varchar(10),class varchar(10),score dec(4,2))
insert @t select 1,'001','01-1',10
union all select 2,'002','01-1',20
union all select 3,'003','01-1',30
union all select 4,'004','01-3',40
union all select 5,'005','01-3',50
union all select 6,'006','01-3',60
union all select 7,'007','01-1',70
union all select 8,'008','01-3',80
--sql statement
select *
from @t A
where id in (select top 3 id from @t where class=A.class order by id desc )
order by class,score desc

[liangpei2008]

--方法2
select *
from @t A
where not exists
(select 1 from @t where class=A.class and score<A.score having count(1)<1)
order by class,score desc

[Harryfin]

楼上的SQL语句想当然地错了 -_-

1. select *, group by里面怎么只能写一个字段
2. top是指最后结果集的前几条，不是分组的前几条
3. order by不能写在group by前面

[zhao_yong]

那樓上有什麽高見呢?