id整数 [date]时间类型
1 '2007-1-1'
2 '2007-1-2'
3 '2007-2-3'
4 '2007-2-4'想得到表
id整数 [date]时间类型 [date1]时间类型
1 '2007-1-1' '2007-1-2'
2 '2007-1-2' '2007-2-3'
3 '2007-2-3' '2007-2-4'
4 '2007-2-4' '2007-2-4'
1 '2007-1-1'
2 '2007-1-2'
3 '2007-2-3'
4 '2007-2-4'想得到表
id整数 [date]时间类型 [date1]时间类型
1 '2007-1-1' '2007-1-2'
2 '2007-1-2' '2007-2-3'
3 '2007-2-3' '2007-2-4'
4 '2007-2-4' '2007-2-4'
from ta
from ta
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,date datetime)
insert into #T
select 1,'2007-1-1' union all
select 2,'2007-1-2' union all
select 3,'2007-2-3' union all
select 4,'2007-2-4'select a.*, isnull(b.date,a.date) as date1 from #T a left join #T b on a.id=b.id-1/*
id date date1
----------- ----------------------- -----------------------
1 2007-01-01 00:00:00.000 2007-01-02 00:00:00.000
2 2007-01-02 00:00:00.000 2007-02-03 00:00:00.000
3 2007-02-03 00:00:00.000 2007-02-04 00:00:00.000
4 2007-02-04 00:00:00.000 2007-02-04 00:00:00.000
*/
INSERT @a SELECT 1, '2007-1-1'
UNION ALL SELECT 2, '2007-1-2'
UNION ALL SELECT 3, '2007-2-3'
UNION ALL SELECT 4, '2007-2-4' SELECT *,
b=
CASE
WHEN EXISTS( SELECT 1 FROM @a WHERE id>a.id) THEN ( SELECT TOP 1 a FROM @a WHERE id>a.id ORDER BY id)
ELSE a
END
FROM @a a--result
/*id a b
----------- -------------------- --------------------
1 2007-1-1 2007-1-2
2 2007-1-2 2007-2-3
3 2007-2-3 2007-2-4
4 2007-2-4 2007-2-4(所影响的行数为 4 行)*/
create table a
(
id int,
date smalldatetime
)
insert into a
select
1,'2007-1-1' union
select
2,'2007-1-2' union
select
3,'2007-2-3' union
select
4,'2007-2-4'select a.*,case when b.date is null then a.date else b.date end as 'date'
from a left join a as b on a.id+1 = b.id
结果
id date date
----------- ----------------------- -----------------------
1 2007-01-01 00:00:00 2007-01-02 00:00:00
2 2007-01-02 00:00:00 2007-02-03 00:00:00
3 2007-02-03 00:00:00 2007-02-04 00:00:00
4 2007-02-04 00:00:00 2007-02-04 00:00:00(4 row(s) affected)
create table a
(
id int,
date smalldatetime
)
insert into a
select
1,'2007-1-1' union
select
2,'2007-1-2' union
select
3,'2007-2-3' union
select
4,'2007-2-4' select aa.*,isnull(bb.date,aa.date) from a aa left join a bb
on aa.id=bb.id-1
where aa.date=date order by id ) else aa.date end) from a aa