求个查询！多谢各位师兄

求个查询！谢谢各位师兄！
我有这样这样的四个表

rrt_exam_class
rrt_examid   rrt_classid
          1                   1
          2                   1

rrt_exam_mark
rrt_examid     rrt_stid     rrt_mark
          1                   1                   98    
          1                   2                   56
          2                   1                   56
          2                   2                   65      

rrt_class
rrt_classid   rrt_classname
          1                 初一(1)班

rrt_student
rrt_stid     rrt_stname
      1               张三
      2               李四
怎样查询成这样

  学生     总分   名次     进步指数
  张三     56         2               -1
  李四     65         1               +1

谢谢了!




[解决办法]
create table rrt_exam_class(rrt_examid int, rrt_classid int)
insert rrt_exam_class select 1, 1
union all select 2, 1

create table rrt_exam_mark(rrt_examid int, rrt_stid int, rrt_mark int)
insert rrt_exam_mark select 1, 1, 98
union all select 1, 2, 56
union all select 2, 1, 56
union all select 2, 2, 65

create table rrt_class(rrt_classid int, rrt_classname varchar(10))
insert rrt_class select 1, '初一(1)班 '

create table rrt_student(rrt_stid int, rrt_stname varchar(10))
insert rrt_student select 1, '张三 '
union all select 2, '李四 '

select 学生=rrt_stname, 总分=tmpA.rrt_mark,
名次=tmpA.第二学期名次,
进步指数=(case when (tmpA.第二学期名次-tmpB.第一学期名次) <0 then '+ ' else '- ' end)+rtrim(abs(tmpA.第二学期名次-tmpB.第一学期名次))
from
(
select tmpA.rrt_stid, rrt_stname, rrt_mark,
第二学期名次=(select count(*) from rrt_exam_mark where rrt_examid=2 and rrt_mark> =tmpA.rrt_mark)
from rrt_exam_mark as tmpA
inner join rrt_student on tmpA.rrt_stid=rrt_student.rrt_stid
where rrt_examid=2
)tmpA,
(
select rrt_stid,
第一学期名次=(select count(*) from rrt_exam_mark where rrt_examid=1 and rrt_mark> =tmpB.rrt_mark)
from rrt_exam_mark as tmpB
where rrt_examid=1
)tmpB
where tmpA.rrt_stid=tmpB.rrt_stid

--result
学生 总分 名次 进步指数
---------- ----------- ----------- -------------
张三 56 2 -1
李四 65 1 +1

(2 row(s) affected)
------解决方案--------------------

Select 学生,总分,名次,进步指数=(Case when 进步指数> 0 then '+ '+ltrim(进步指数)
when 进步指数 <0 then ltrim(进步指数) else '-- ' end)
from (
Select 学生,总分,名次,进步指数=名次-IsNULL(上次名次,名次)
from ( select b.rrt_examid,学生=a.rrt_stname,总分=b.rrt_mark,
名次=(Select Count(*) from rrt_exam_mark where rrt_examid=b.rrt_examid and
rrt_mark> =b.rrt_mark),
上次名次=(Select Count(*) from rrt_exam_mark as r
where rrt_examid=b.rrt_examid-1 and (Select rrt_mark from rrt_exam_mark
where rrt_examid=b.rrt_examid-1 and rrt_stid=b.rrt_stid) <=r.rrt_mark )
from rrt_student as a inner Join rrt_exam_mark as b on a.rrt_stid=b.rrt_stid ) as t
Where not exists(Select * from rrt_exam_class where rrt_examid> t.rrt_examid) ) as tb