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,98union select 1,1,98 union select 1,2,52union 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 名次
