我现在有一张表Table其中一个字段为Date。假设它的数据如下(当然,真实的记录数是不定的):
Date
2008-1-1
2008-1-10
2008-1-31
2008-2-2
2008-2-11
2008-2-23我如何写语句得到如下结果(通过语句增加一列,而且新加的一列可以指定从第二条记录开始列示):
Date Date2
2008-1-1 2008-1-10
2008-1-10 2008-1-31
2008-1-31 2008-2-2
2008-2-2 2008-2-11
2008-2-11 2008-2-23
2008-2-23 NULL当然,有时我会需要加上条件,例如,如果我加了条件Date<=2008-2-1,那么得到的结果如下:
Date Date2
2008-2-2 2008-2-11
2008-2-11 2008-2-23
2008-2-23 NULL哪个高人可以指点一下呢?在此先谢过了。PS:比较着急,谢谢了。
Date
2008-1-1
2008-1-10
2008-1-31
2008-2-2
2008-2-11
2008-2-23我如何写语句得到如下结果(通过语句增加一列,而且新加的一列可以指定从第二条记录开始列示):
Date Date2
2008-1-1 2008-1-10
2008-1-10 2008-1-31
2008-1-31 2008-2-2
2008-2-2 2008-2-11
2008-2-11 2008-2-23
2008-2-23 NULL当然,有时我会需要加上条件,例如,如果我加了条件Date<=2008-2-1,那么得到的结果如下:
Date Date2
2008-2-2 2008-2-11
2008-2-11 2008-2-23
2008-2-23 NULL哪个高人可以指点一下呢?在此先谢过了。PS:比较着急,谢谢了。
from table a
insert into tb select '2008-1-1'
insert into tb select '2008-1-10'
insert into tb select '2008-1-31'
insert into tb select '2008-2-2'
insert into tb select '2008-2-11'
insert into tb select '2008-2-23' select id = identity(int,1,1),* into #tb1 from tb
select id = identity(int,1,1),* into #tb2 from tbselect * from #tb1
select * from #tb2select A.dt dt1,B.dt dt2 from #tb1 A left join #tb2 B on A.id = B.id-1drop table #tb1,#tb2,tb
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([Date] Datetime)
Insert #T
select '2008-1-1' union all
select '2008-1-10' union all
select '2008-1-31' union all
select '2008-2-2' union all
select '2008-2-11' union all
select '2008-2-23'
Go
Select * from #Tselect a.date,min(b.date) from #T a ,#T b
where a.date<b.date and a.date <'2008-02-02'
group by a.date
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (Date datetime)
insert into #T
select '2008-1-1' union all
select '2008-1-10' union all
select '2008-1-31' union all
select '2008-2-2' union all
select '2008-2-11' union all
select '2008-2-23'
go
select date,nextdate=(select min(date) from #T where date>t.date)
from #T t
go
drop table #T/*
date nextdate
----------------------- -----------------------
2008-01-01 00:00:00.000 2008-01-10 00:00:00.000
2008-01-10 00:00:00.000 2008-01-31 00:00:00.000
2008-01-31 00:00:00.000 2008-02-02 00:00:00.000
2008-02-02 00:00:00.000 2008-02-11 00:00:00.000
2008-02-11 00:00:00.000 2008-02-23 00:00:00.000
2008-02-23 00:00:00.000 NULL(6 row(s) affected)
*/
嗯,这个方法好像可以,我再看看加入到我的完整语句中是不是可以满足要求。
谢谢您。同时也谢谢上面给我提供多种方法的专家。PS:你的脚本有一个小遗漏:min([Date] 少了一个反括号,呵呵。