嵌套查询问题???

[tamebo]

表:
ID Time
4 2007-1-1
1 2007-1-3
2 2007-1-4
4 2007-1-5

当插入数据后 2007-1-2
自动取出取出最靠近的时间段 2007-1-1 和 2007-1-3 的id号4和1

[comszsoft]

create proc p_add
@time datetime,
@previd int output,
@nextid int output
as

insert into t1(time) values(@time)

set @previd=isnull((select top 1 [id] from t1 where time < @time order by time ),0)
set @nextid=isnull((select top 1 [id] from t1 where time > @time order by time ),0)

go

[paoluo]

Create Table TEST
(ID Int,
Time Varchar(10))
Insert TEST Select 4, '2007-1-1'
Union All Select 1, '2007-1-3'
Union All Select 2, '2007-1-4'
Union All Select 4, '2007-1-5'
GO
Declare @Time DateTime
Select @Time = '2007-1-2'
Select
ID
From
TEST
Where
[Time]
In (Select TOP 1 [Time] From TEST Where [Time] > @Time Order By [Time]
Union All
Select TOP 1 [Time] From TEST Where [Time] < @Time Order By [Time] Desc)
GO
Drop Table TEST
/*
ID
4
1
*/

[dawugui]

if object_id('pubs..tb') is not null
drop table tb
go

create table tb(ID int,Time datetime)
insert into tb(ID,Time) values(4, '2006-1-1')
insert into tb(ID,Time) values(4, '2007-1-1')
insert into tb(ID,Time) values(1, '2007-1-3')
insert into tb(ID,Time) values(2, '2007-1-4')
insert into tb(ID,Time) values(4, '2007-1-5')
go

declare @dt as datetime
set @dt = '2007-01-02'

select * from tb where time in
(
select max(time) as time from tb where datediff(day, time , @dt) > 0
union all
select min(time) as time from tb where datediff(day, time , @dt) < 0
)

drop table tb

/*
ID Time
----------- ------------------------------------------------------
4 2007-01-01 00:00:00.000
1 2007-01-03 00:00:00.000

（所影响的行数为 2 行）
*/