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((T3.DP1-T2.DP1)-(T2.DP1-T1.DP1),0)) As DP1差值,(IsNull((T2.DP3-T2.DP1)-(T1.DP3-T1.DP1),0)) As DP3DP1差值 Into B
from A T1
Left Join A T2
On DateDiff(dd,T1.Nyr,T2.Nyr)=1
Left Join A T3
On DateDiff(dd,T2.Nyr,T3.Nyr)=1Select * from B
--删除测试环境
Drop Table A,B
--结果
/*
Nyr DP1差值DP3DP1差值
01/01/19614125
01/02/1961-43-7
01/03/1961-56-59
01/04/19617077
01/05/1961-40-55
01/06/19614429
01/07/19613621
01/08/1961-54-35
01/09/1961068
01/10/196100
*/一般的SQL语句都在放在rs.open后面就行了,上面这部分该怎么做,才能得到上面的结果呢?
from A T1
Left Join A T2
On DateDiff(dd,T1.Nyr,T2.Nyr)=1
Left Join A T3
On DateDiff(dd,T2.Nyr,T3.Nyr)=1"不懂你into B干嗎
直接rs.open strSql,cn
這樣就可以了啊
strSql = "Select T1.Nyr,(IsNull((T3.DP1-T2.DP1)-(T2.DP1-T1.DP1),0)) As DP1差值,(IsNull((T2.DP3-T2.DP1)-(T1.DP3-T1.DP1),0)) As DP3DP1差值
from A T1
Left Join A T2
On DateDiff(dd,T1.Nyr,T2.Nyr)=1
Left Join A T3
On DateDiff(dd,T2.Nyr,T3.Nyr)=1"