怎么由给定日期，筛选记录

如何由给定日期，筛选记录？
现有一个table表，有列（id，date）
如果同一个id有多条记录，那么给定一个日期[date_fin]，选择[date_fin]之后且最近的记录；
若[date_fin]之后都没有记录，则选择[date_fin]之前且最近的记录？

[解决办法]

SQL code

if OBJECT_id('tb') is not null Drop table tb;create table tb(id int,[date] datetime)insert into TB values(810,'2004-02-15')insert into TB values(810,'2004-03-02')insert into TB values(810,'2004-04-18')insert into TB values(810,'2004-05-06')insert into TB values(810,'2004-06-20')insert into TB values(450,'2004-03-22')insert into TB values(450,'2005-06-22')insert into TB values(450,'2005-07-22')insert into TB values(520,'2004-03-02')insert into TB values(520,'2004-03-22');--取date_fin='2004-04-25'declare @date_fin datetime;set @date_fin = '2004-04-25';with T1(id, date) as(    select id, min(date) from tb    where date >= @date_fin    group by id)select * from T1Union allselect a.id, max(a.date) from tb awhere a.date < @date_fin    and (not exists(select 1 from T1 where a.id = T1.id))group by a.id/*id          date----------- -----------------------450         2005-06-22 00:00:00.000810         2004-05-06 00:00:00.000520         2004-03-22 00:00:00.000*/
[解决办法]
SQL code
--取date_fin='2004-04-25'select distinct B.id,B.[date] --大于日期最近的 ，也可以两种写法交换。（修改后）from TB Across apply(select top 1 id,[date] from TB where A.id =id and [date]>'2004-04-25' order by id,[date] ) Bunion allselect id,max([date]) as date --小于日期最近的from TB Awhere not exists(select 1 from TB where id = A.id and [date] >'2004-04-25' )group by id/*450    2005-06-22 00:00:00.000810    2004-05-06 00:00:00.000*/
[解决办法]
SQL code
create table TB(id int,[date] datetime)insert into TB values(810,'2004-02-15')insert into TB values(810,'2004-03-02')insert into TB values(810,'2004-04-18')insert into TB values(810,'2004-05-06')insert into TB values(810,'2004-06-20')insert into TB values(450,'2005-06-22')declare @date char(10)declare @sql varchar(100)set @date='2005-08-10'  if  exists (select *  from tb where date >=''+@date+'')  begin     set @sql=' select id from tb where date=(select min(date) as date from tb where date >='''+@date+''')'  end else  begin       set @sql='select id from tb where date=(select max(date) as date from tb where date <='''+@date+''')'  endprint @sqlexec (@sql)