表A: id starTime endTime 都是int字段
1 20091231 20100102
2 20100113 20100115
3 20100110 20100117
需求:查询出离今天最近的数据,如:今天2010-01-11 转成20100111 再分别与starttime 和endtime比较,那个最接近今天查询哪个。 查出 第3条。
1 20091231 20100102
2 20100113 20100115
3 20100110 20100117
需求:查询出离今天最近的数据,如:今天2010-01-11 转成20100111 再分别与starttime 和endtime比较,那个最接近今天查询哪个。 查出 第3条。
go
create table [tb] (id int,starTime int,endTime int)
insert into [tb]
select 1,20091231,20100102 union all
select 2,20100113,20100115 union all
select 3,20100110,20100117select * from [tb]
where cast(convert(varchar(10),getdate(),112)as int)
between starTime and endTime
/*
id starTime endTime
----------- ----------- -----------
3 20100110 20100117(1 個資料列受到影響)
*/
where cast(convert(varchar(6),getdate(),112)as int)
between starTime and endTime
?
where cast(convert(varchar(8),getdate(),112)as int)
between starTime and endTime?
----------- ----------- -----------
2 20100113 20100115
3 20100110 20100117(2 行受影响)
insert into [tb]
select 1,20091231,20100102 union all
select 2,20100113,20100115 union all
select 3,20100110,20100117select top 1 * from tb
order by case
when abs(cast(convert(varchar(8),getdate(),112) as int) - starTime) < abs(cast(convert(varchar(8),getdate(),112) as int) - endTime)
then abs(cast(convert(varchar(8),getdate(),112) as int) - starTime)
else abs(cast(convert(varchar(8),getdate(),112) as int) - endTime)
enddrop table tb/*
id starTime endTime
----------- ----------- -----------
3 20100110 20100117(所影响的行数为 1 行)
*/
楼主这个应该是一个例子,实际会有很多数据!
只差 最小的
if object_id('tb') is not null drop table tb
go
create table tb([id] INT,[starTime] INT,[endTime] INT)
insert into tb
select 1,20091231,20100112 union all
select 2,20100113,20100115 union all
select 3,20100109,20100117 union all
select 4,20100112,20100117
go
select * from tb
/*
id starTime endTime
----------- ----------- -----------
1 20091231 20100112
2 20100113 20100115
3 20100109 20100117
4 20100112 20100117
*/
;with cte as
(
select *,case when abs(cast(convert(varchar(10),getdate(),112)as int) - startime) < abs(cast(convert(varchar(10),getdate(),112)as int) - endTime)
then abs(cast(convert(varchar(10),getdate(),112)as int) - startime) else abs(cast(convert(varchar(10),getdate(),112)as int) - endTime) end as v
from tb
)
select id,startime,endtime from cte
where v = (select min(v) from cte)
/*
id startime endtime
----------- ----------- -----------
1 20091231 20100112
4 20100112 20100117
*/
select top 1 with ties * from tb
order by case
when abs(cast(convert(varchar(8),getdate(),112) as int) - starTime) < abs(cast(convert(varchar(8),getdate(),112) as int) - endTime)
then abs(cast(convert(varchar(8),getdate(),112) as int) - starTime)
else abs(cast(convert(varchar(8),getdate(),112) as int) - endTime)
end