select class,sscore from(
select id=dense_rank() over(order by sscore), class,sscore=SUM(score) from student a join sc b on a.S#=b.S#
) a where id<=3

------解决方案--------------------
SQL code
--sql 2000
select class , sname , s# , score , px from
(
select t1.* , (select count(score) from 
(
  select m.Class , m.S# , m.Sname , sum(n.score) score from student m , sc n where m.s# = n.s# group by m.Class , m.S# , m.Sname
) t1 where t1.class = t2.class and t1.score < t2.score) + 1 px
from
(
  select m.Class , m.S# , m.Sname , sum(n.score) score from student m , sc n where m.s# = n.s# group by m.Class , m.S# , m.Sname
) t2
) k
where px <= 3

--sql 2005
select class , sname , s# , score , px from
(
  select t.* , RANK () OVER(partition by m.Class order by n.score) px from
  ( 
    select m.Class , m.S# , m.Sname , sum(n.score) score from student m , sc n where m.s# = n.s# group by m.Class , m.S# , m.Sname
  ) t
) k
where px <= 3

------解决方案--------------------
这里应该有相关内容.
http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html
一个项目涉及到的50个Sql语句

------解决方案--------------------

SQL code

use tempdb;
/*
create table student(S# int,Sname varchar(10),Class varchar(10))
create table sc(S# int,C# varchar(10),score int)

insert into student values(1,'美竹凉子','一年一班')
insert into student values(2,'武藤兰','一年一班')
insert into student values(3,'松金洋子','一年一班')
insert into student values(4,'秋野圭子','一年一班')
insert into student values(5,'伊东林','一年二班')
insert into student values(6,'松岛枫','一年二班')
insert into student values(7,'苍井空','一年二班')
insert into student values(8,'吉泽明步','一年二班')
insert into student values(9,'惠美梨','一年二班')
insert into student values(10,'饭岛爱','一年三班')
insert into student values(11,'小泽圆','一年三班')

insert into Sc values(1,60011,96)
insert into Sc values(2,60011,88)
insert into Sc values(3,60011,89)
insert into Sc values(4,60011,79)
insert into Sc values(5,60011,67)
insert into Sc values(6,60011,99)
insert into Sc values(7,60011,74)
insert into Sc values(8,60011,87)
insert into Sc values(9,60011,67)
insert into Sc values(10,60011,90)
insert into Sc values(11,60011,60)
insert into Sc values(1,60013,88)
insert into Sc values(2,60013,97)
insert into Sc values(3,60013,67)
insert into Sc values(4,60013,89)
insert into Sc values(5,60013,68)
insert into Sc values(6,60013,70)
insert into Sc values(7,60013,71)
insert into Sc values(8,60013,73)
insert into Sc values(9,60013,78)
insert into Sc values(10,60013,82)
insert into Sc values(11,60013,76)
*/
select t.* 
from
(
    select t1.S#,t1.Sname,t1.Class,t3.totalscore 
    from student as t1
    left join
    (
        select t2.S#,SUM(t2.score) as [totalscore] from SC as t2 group by t2.S#
    ) as t3 on t1.S# = t3.S#    
) as t
where 
t.totalscore in
(
    select top 3  with ties temp.totalscore
    from
    (
        select t1.S#,t1.Sname,t1.Class,t3.totalscore 
        from student as t1
        left join
        (
            select t2.S#,SUM(t2.score) as [totalscore] from SC as t2 group by t2.S#
        ) as t3 on t1.S# = t3.S#
    ) as temp where t.Class = temp.Class
    order by temp.totalscore desc
);