--1.学生表
Student(S#,Sname,Class) --S# 学生编号,Sname 学生姓名,Class 班级
--2.成绩表
SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数
查询每个班级总成绩倒数三名的学生姓名
------解决方案--------------------
- SQL code
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 2000select 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 pxfrom( 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) kwhere px <= 3--sql 2005select 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) kwhere 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 twhere 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);