求语句

[t0h]

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'

[-狙击手-]

select id,[date],dateadd(d,1,[date]) as date1
from ta

[-狙击手-]

select id,convert(char(10),[date],120) as [date],convert(char(10),dateadd(d,1,[date]),120) as date1
from ta

[yms_wangxm]

啥呀，怎么看不到什么规则？？

[Limpire]

--> 测试数据: #T
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
*/

[chuifengde]

DECLARE @a TABLE(id int,a varchar(20))
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 行）*/

[sweetweiwei]

--try
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)

[qdtianjinhua]

[Quote=引用 5 楼 chuifengde 的回复:]
SQL codeDECLARE @a TABLE(id int,a varchar(20))
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 …
[/Quote]

[Herb2]

鸟的和甜微微的是正解

[jiamingwudi]

xue xi

[ojuju10]

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

[ojuju10]

select aa.*,(case when exists(select 1 from a  where id>aa.id  ) then (select top 1 dateadd(day,1,date) from a
where aa.date=date order by id  )  else aa.date end)  from a aa