排名以及进步情况写法！

实现排名以及进步的情况 here  学生 总分 名次 进步指数   王五 98  1  ---  李四 65  2  －1  张三 56  3  ＋1 :实现结果图

 

 

 

 

 

 

 

--定义表结构

Declare @rrt_exam_class table(rrt_examid int ,rrt_classid int )
Insert into @rrt_exam_class             --班级测试情况表
Select 1,1 union select 2,1

Declare @rrt_exam_mark table(rrt_examid int ,rrt_stid int,rrt_mark int )
Insert into @rrt_exam_mark               --测试成绩表
      Select 1,3,99 union select 2,3,98
union select 1,1,98 union select 1,2,52
union select 2,1,56 union select 2,2,65  

Declare @rrt_class table(rrt_classid int ,rrt_classname varchar(20) )
Insert into @rrt_class Select  1,'初一(1)班'    --班级表

Declare @rrt_student table(rrt_stid int ,rrt_stname varchar(20) ) --学生表
Insert into @rrt_student
Select 1,'张三' union select 2,'李四' union Select 3,'王五' 

 

--实现
 

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 Order by 名次