比如表:Table1
ID stuID store stime
1 1 50 2007-1-1
2 1 60 2007-3-4
3 1 70 2007-8-9我想算stuID = 1的每次考试进步多少,如下:
stuID store stime
1 0 2007-1-1
1 20 2007-3-4
1 16.67 2007-8-9进步率=(后一次分数-前一次分数)*100/前一次分数期待相助,谢谢大家!
ID stuID store stime
1 1 50 2007-1-1
2 1 60 2007-3-4
3 1 70 2007-8-9我想算stuID = 1的每次考试进步多少,如下:
stuID store stime
1 0 2007-1-1
1 20 2007-3-4
1 16.67 2007-8-9进步率=(后一次分数-前一次分数)*100/前一次分数期待相助,谢谢大家!
create table #tab(id int,stuid int ,store int,stime datetime)insert into #tab values(1,1,50,'2007-1-1')
insert into #tab values(2,1,60,'2007-3-4')
insert into #tab values(3,1,70,'2007-8-9')select * from #tabselect stuid,store =cast(1.0*(store-isnull((select top 1 store from #tab where stuid=a.stuid and id<a.id order by id desc),store))*100/isnull((select top 1 store from #tab where stuid=a.stuid and id<a.id order by id desc),1) as numeric(5,2) ),stime from #tab a where stuid=1
stuid store stime
----------- --------------------------------------- -----------------------
1 0.00 2007-01-01 00:00:00.000
1 20.00 2007-03-04 00:00:00.000
1 16.67 2007-08-09 00:00:00.000(3 行受影响)
create table #tab(id int,stuid int ,store int,stime datetime)insert into #tab values(1,1,50,'2007-1-1')
insert into #tab values(2,1,60,'2007-3-4')
insert into #tab values(3,1,70,'2007-8-9')select * from #tabselect stuid,store =cast(1.0*(store-isnull((select top 1 store from #tab where stuid=a.stuid and stime<a.stime order by id desc),store))*100/isnull((select top 1 store from #tab where stuid=a.stuid and stime<a.stime order by id desc),1) as numeric(5,2) ),stime from #tab a where stuid=1stuid store stime
----------- --------------------------------------- -----------------------
1 0.00 2007-01-01 00:00:00.000
1 20.00 2007-03-04 00:00:00.000
1 16.67 2007-08-09 00:00:00.000(3 行受影响)
insert into tb values(1 , 1 , 50 , '2007-1-1')
insert into tb values(2 , 1 , 60 , '2007-3-4')
insert into tb values(3 , 1 , 70 , '2007-8-9')
goselect m.stuID , m.store , m.stime ,
进步率 = case when (select top 1 store from tb where stime < m.stime order by stime desc) is null then 0 else
cast(isnull(m.store - (select top 1 store from tb where stime < m.stime order by stime desc),0)*100.0 / (select top 1 store from tb where stime < m.stime order by stime desc) as decimal(18,2))
end
from tb m drop table tb/*
stuID store stime 进步率
----------- ----------- ------------------------------------------------------ --------------------
1 50 2007-01-01 00:00:00.000 .00
1 60 2007-03-04 00:00:00.000 20.00
1 70 2007-08-09 00:00:00.000 16.67(所影响的行数为 3 行)
*/
insert into #tab values(2,1,60,'2007-3-4')
insert into #tab values(3,1,70,'2007-8-9')select * from #tabselect stuid,store =cast(1.0*(store-isnull((select top 1 store from #tab where stuid=a.stuid and id<a.id order by id desc),store))*100/isnull((select top 1 store from #tab where stuid=a.stuid and id<a.id order by id desc),1) as numeric(5,2) ),stime from #tab a where stuid=1
stuid store stime
----------- --------------------------------------- -----------------------
1 0.00 2007-01-01 00:00:00.000
1 20.00 2007-03-04 00:00:00.000
1 16.67 2007-08-09 00:00:00.000(3 行受影响)
insert into tb values(1 , 1 , 50 , '2007-1-1')
insert into tb values(2 , 1 , 60 , '2007-3-4')
insert into tb values(3 , 1 , 70 , '2007-8-9')
goselect m.stuID , m.store , m.stime ,
进步率 = case when (select top 1 store from tb where stime < m.stime order by stime desc) is null then 0 else
cast((m.store - (select top 1 store from tb where stime < m.stime order by stime desc))*100.0 / (select top 1 store from tb where stime < m.stime order by stime desc) as decimal(18,2))
end
from tb m drop table tb/*
stuID store stime 进步率
----------- ----------- ------------------------------------------------------ --------------------
1 50 2007-01-01 00:00:00.000 .00
1 60 2007-03-04 00:00:00.000 20.00
1 70 2007-08-09 00:00:00.000 16.67(所影响的行数为 3 行)
*/
----------- ---------------------- -----------------------
1 NULL 2007-01-01 00:00:00.000
1 20 2007-03-04 00:00:00.000
1 16.67 2007-08-09 00:00:00.000(3 行受影响)