现有一个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 >[email protected]+'') begin set @sql=' select id from tb where date=(select min(date) as date from tb where date >[email protected]+''')' end else begin set @sql='select id from tb where date=(select max(date) as date from tb where date <[email protected]+''')' endprint @sqlexec (@sql)