各位老师好。
我的表中有一个datetime类型的字段rs1,我想实现如下功能:让(记录N)与(记录N-1)的rs1字段相减,得出它们的差值。如记录2的rs1字段与记录1的rs1字段相减,记录3的rs1字段与记录2的rs1字段相减。怎样实现这个功能,请各位老师指教。
我的表中有一个datetime类型的字段rs1,我想实现如下功能:让(记录N)与(记录N-1)的rs1字段相减,得出它们的差值。如记录2的rs1字段与记录1的rs1字段相减,记录3的rs1字段与记录2的rs1字段相减。怎样实现这个功能,请各位老师指教。
select rs1,rs1-(select top 1 rs1 from table where id < a.id order by id desc)
from table a
create table test(id int,date datetime)
insert into test
select 1 ,'1901-01-01'
union all select 2,'1903-01-13'
union all select 3,'1932-06-21'
union all select 4,'1989-01-11' select id, date,isnull(datediff(ss,date,cast((select date from test where id = a.id-1) as datetime)),'')
from test a order by id asc
drop table test
/*
id date
----------- ----------------------- -----------
1 1901-01-01 00:00:00.000 0
2 1903-01-13 00:00:00.000 -64108800
3 1932-06-21 00:00:00.000 -928972800
4 1989-01-11 00:00:00.000 -1784851200
*/
insert @t select 1,'2001-01-01'
insert @t select 2,'2001-01-3'
insert @t select 3,'2001-01-5'
insert @t select 4,'2001-01-6'
select rs1,datediff(d,isnull((select top 1 rs1 from @t where id < a.id order by id desc),'2001-01-01'),rs1)
from @t ars1
------------------------------------------------------ -----------
2001-01-01 00:00:00.000 0
2001-01-03 00:00:00.000 2
2001-01-05 00:00:00.000 2
2001-01-06 00:00:00.000 1(所影响的行数为 4 行)
insert @t select 1,'2001-01-01'
insert @t select 2,'2001-01-3'
insert @t select 5,'2001-01-5'
insert @t select 6,'2001-01-6'
select rs1,datediff(d,isnull((select top 1 rs1 from @t where id < a.id order by id desc),'2001-01-01'),rs1)
from @t a
/*
rs1
------------------------------------------------------ -----------
2001-01-01 00:00:00.000 0
2001-01-03 00:00:00.000 2
2001-01-05 00:00:00.000 2
2001-01-06 00:00:00.000 1(所影响的行数为 4 行)*/
drop table test
go
create table test(id int,date datetime)
insert into test
select 1 ,'1901-01-01'
union all select 2,'1903-01-13'
union all select 3,'1932-06-21'
union all select 4,'1989-01-11'
select * from testselect A.id,A.date,isnull(datediff(mm,cast((select date from test where id=A.id-1)as datetime),date),'0') as 差值 from test A