1. select identity(int,1,1) as num_id, time,datas into #tmp from table
2. select a.time as starttime, b.time as endtime, a.datas as startdata, b.datas as enddata, abs(a.datas - b.datas) as diff
from #tmp a, #tmp b
where a.num_id = b.num_id - 1
2. select a.time as starttime, b.time as endtime, a.datas as startdata, b.datas as enddata, abs(a.datas - b.datas) as diff
from #tmp a, #tmp b
where a.num_id = b.num_id - 1
b.time as endtime,
b.datas as startdata,
a.datas as enddata,
b.datas-a.datas as diff
FROM TableName a,TableName b
WHERE b.time-a.time=1
a.datas as startdata,b.datas as enddata,
b.datas-a.datas as diff
from #table a, #table b
where b.id=a.id+1
into #TS
from Utable
where starttime <> (select max(starttime) from Utable A))
order by starttimeselect identity(int,1,1) as tid ,starttime,dates
into #TE
from Utable
where starttime <> (select min(starttime) from Utable A))
order by starttimeselect A.starttime,B.starttime,A.dates,B.dates,A.dates-B.dates as diffdates
from #TS A ,#TE B
where A.tid = B.tiddrop table #TS,#TE
insert @a values('20021201',20)
insert @a values('20021202',5)
insert @a values('20021203',10)
insert @a values('20021204',14)
insert @a values('20030101',10)
insert @a values('20030102',60)
insert @a values('20030103',50)select * from (select time starttime,(select top 1 time from @a B where A.time<B.time) endtime,datas startdata,
(select top 1 datas from @a B where A.time<B.time) enddata,
(select top 1 abs(A.datas-datas) from @a B where A.time<B.time) diff
from @a A) tem where endtime is not null
呵呵
再改一下select idnetity(int,1,1) as id ,* into #temp from tableselect a.time as startdtime,b.time as endtime,
a.datas as startdata,b.datas as enddata,
substring('- +',sign(b.data-a.data)+2,1)+cast(abs(b.data-a.data) as varchar)
starttime endtime startdata enddata diff
20021201 20021202 20 5 -15
20021202 20021203 5 10 +5
20021203 20021204 10 14 +4
20021204 20030101 14 10 -4
20030101 20030102 10 60 +50
20030102 20030103 60 50 -10
比较好看吧 哈哈
from #table a, #table b
where b.id=a.id+1得到
FROM t LEFT OUTER JOIN t t1 ON t.time = DATEADD(d, - 1, t1.time)
ABS(a.datas - b.datas) AS datas
FROM [TABLE] a, [TABLE] b
WHERE b.time =
(SELECT MIN(time)
FROM [TABLE]
WHERE time > a.time)