--创建测试环境
create table #
(
ID int identity,
[date] datetime
)
insert #([date])
select '2005-1-1' union select '2005-2-1' union select '2005-3-8'select * from #
--测试
select [差值]=Datediff(day,A.[date],B.[Date]) from # A join # B on A.ID+1=B.ID--删除测试环境
drop table # --结果
/*
(所影响的行数为 3 行)ID date
----------- ------------------------
1 2005-01-01 00:00:00.000
2 2005-02-01 00:00:00.000
3 2005-03-08 00:00:00.000(所影响的行数为 3 行)差值
-----------
31
35(所影响的行数为 2 行)
*/
create table #
(
ID int identity,
[date] datetime
)
insert #([date])
select '2005-1-1' union select '2005-2-1' union select '2005-3-8'select * from #
--测试
select [差值]=Datediff(day,A.[date],B.[Date]) from # A join # B on A.ID+1=B.ID--删除测试环境
drop table # --结果
/*
(所影响的行数为 3 行)ID date
----------- ------------------------
1 2005-01-01 00:00:00.000
2 2005-02-01 00:00:00.000
3 2005-03-08 00:00:00.000(所影响的行数为 3 行)差值
-----------
31
35(所影响的行数为 2 行)
*/
简单的说就是有张表格A.所求子段DP 时间从1961-1-1到2005-7-5,后面每天的数据照样要录入.怎么求得相邻两条记录DP子段的差值,就象1961-1-2和1961-1-1号DP差值,1961-1-3和1961-1-2号DP差值,1961-1-4和1961-1-3号DP差值......2005-7-5和2005-7-4号的DP差值.怎么写啊大家帮忙写详细点好吗?先谢过了!!!
set nocount on
go
create table A
(
dp datetime
)
insert A(dp)
select '1961-1-1' union select '1961-1-2' union select '1961-1-3' union select '1961-1-4'select * from A--测试
declare @tb table(ID int identity,dp datetime)
insert @tb(dp) select * from A
select [差值(天)]=Datediff(day,T.dp,B.dp) from @tb T join @tb B on T.ID+1=B.ID--删除测试环境
drop table A--结果
/*dp
------------------------------------------------------
1961-01-01 00:00:00.000
1961-01-02 00:00:00.000
1961-01-03 00:00:00.000
1961-01-04 00:00:00.000差值(天)
-----------
1
1
1
*/
from A T1
Left Join A T2
On DateDiff(dd,T1.时间字段,T2.时间字段)=1
01/02/1961 89 78 58 20 30 36
01/03/1963 106 111 130 45 32 49
. . . . . . .
. . . . . . .
. . . . . . .
07/01/2005 150 136 98 18 32 27
. . . . . . .
. . . . . . .
. . . . . . .
(后续记录还要每天再添加)怎么求每两条记录的DP1(字段)差值.也就是后一条记录的DP1要减去前一条记录DP1所形成的一张表.
--创建测试环境
set nocount on
go
create table A
(
Nyr varchar(10),
DP1 int,Dp2 int,DP3 int
)
insert A
select '01/01/1961',55,60,45 union
select '01/02/1961',89,78,58 union
select '01/03/1961',106,111,130 select * from A--测试
declare @tb table(ID int identity,dp int)
insert @tb(dp) select DP1 from A
select [差值(DP1)]=Datediff(day,T.dp,B.dp) from @tb T join @tb B on T.ID+1=B.ID--删除测试环境
drop table A--结果
/*Nyr DP1 Dp2 DP3
---------- ----------- ----------- -----------
01/01/1961 55 60 45
01/02/1961 89 78 58
01/03/1961 106 111 130差值(DP1)
-----------
34
17
*/
Create Table A
(
Nyr Varchar(10),
DP1 Int,
Dp2 Int,
DP3 Int
)
--插入数据
Insert A
Select '01/01/1961',55,60,45 Union
Select '01/02/1961',89,78,58 Union
Select '01/03/1961',106,111,130
--测试
Select T1.Nyr,(IsNull(T2.DP1,T1.DP1)-T1.DP1) As 差值 Into B
from A T1
Left Join A T2
On DateDiff(dd,T1.Nyr,T2.Nyr)=1
Select * from B
--删除测试环境
Drop Table A,B
--结果
/*
Nyr 差值
01/01/1961 34
01/02/1961 17
01/03/1961 0
*/