set nocount on
go
create table A
(
Nyr varchar(10),
DP1 int,dp2 int,dp3 int
)insert A
select '01/01/1961',36,13,13 union
select '01/02/1961',61,21,32 union
select '01/03/1961',54,73,37 union
select '01/04/1961',25,36,58 union
select '01/05/1961',55,90,112select * from A--测试
declare @tb table(ID int identity,dp int)
insert @tb(dp) select DP1 from Aselect [差值(DP1)]=Datediff(day,T.dp,B.dp) from @tb T join @tb B on T.ID+1=B.IDselect [ID]=identity(int,1,1),[DP]=Datediff(day,T.dp,B.dp) into # from @tb T join @tb B on T.ID+1=B.ID
select [差值(DP1)]=B.DP-T.DP from # T join # B on T.ID+1=B.ID
drop table #--删除测试环境
drop table A--结果
/*Nyr DP1 dp2 dp3
---------- ----------- ----------- -----------
01/01/1961 36 13 13
01/02/1961 61 21 32
01/03/1961 54 73 37
01/04/1961 25 36 58
01/05/1961 55 90 112差值(DP1)
-----------
25
-7
-29
30差值(DP1)
-----------
-32
-22
59
*/
go
create table A
(
Nyr varchar(10),
DP1 int,dp2 int,dp3 int
)insert A
select '01/01/1961',36,13,13 union
select '01/02/1961',61,21,32 union
select '01/03/1961',54,73,37 union
select '01/04/1961',25,36,58 union
select '01/05/1961',55,90,112select * from A--测试
declare @tb table(ID int identity,dp int)
insert @tb(dp) select DP1 from Aselect [差值(DP1)]=Datediff(day,T.dp,B.dp) from @tb T join @tb B on T.ID+1=B.IDselect [ID]=identity(int,1,1),[DP]=Datediff(day,T.dp,B.dp) into # from @tb T join @tb B on T.ID+1=B.ID
select [差值(DP1)]=B.DP-T.DP from # T join # B on T.ID+1=B.ID
drop table #--删除测试环境
drop table A--结果
/*Nyr DP1 dp2 dp3
---------- ----------- ----------- -----------
01/01/1961 36 13 13
01/02/1961 61 21 32
01/03/1961 54 73 37
01/04/1961 25 36 58
01/05/1961 55 90 112差值(DP1)
-----------
25
-7
-29
30差值(DP1)
-----------
-32
-22
59
*/
Create Table A
(
Nyr Varchar(10),
DP1 Int,
DP2 Int,
DP3 Int,
DP4 Int
)
--插入数据
Insert A
Select '01/01/1961', 55, 50, 91, 69 Union
Select '01/02/1961', 60, 54, 121, 108 Union
Select '01/03/1961', 106, 111, 160, 151 Union
Select '01/04/1961', 109, 13, 104, 66 Union
Select '01/05/1961', 56, 49, 128, 92 Union
Select '01/06/1961', 73, 69, 90, 54 Union
Select '01/07/1961', 50, 49, 96, 75 Union
Select '01/08/1961', 71, 76, 138, 135 Union
Select '01/09/1961', 128, 125, 160, 135 Union
Select '01/10/1961', 131, 128, 231, 171
--测试
Select T1.Nyr,(IsNull((T2.DP3-T2.DP1),(T1.DP3-T1.DP1))-(T1.DP3-T1.DP1)) As 差值 Into B
from A T1
Left Join A T2
On DateDiff(dd,T1.Nyr,T2.Nyr)=1Select * from B
--删除测试环境
Drop Table A,B
--结果
/*
Nyr 差值
01/01/1961 25
01/02/1961 -7
01/03/1961 -59
01/04/1961 77
01/05/1961 -55
01/06/1961 29
01/07/1961 21
01/08/1961 -35
01/09/1961 68
01/10/1961 0
*/