34,593
社区成员
发帖
与我相关
我的任务
分享
create table t(id date);
insert into t
select '2010-9-3'
union all
select '2010-9-1'
union all
select '2010-9-5'
union all
select '2010-9-22';
select a.ID,b.ID as 最近
from t as a
Cross apply
(select *,DENSE=DENSE_RANK()over(order by abs(DATEDIFF(D,a.ID,ID)) ) from t where ID<>a.ID ) as b
where b.DENSE=1
order by 1
/*
解释:1.cross apply是sql server 2005之后增加的一个关键词,前身是corss join。
2.dense_rank是一个结果集排序函数。
3.通过把一个表与一个(假)函数链接,避免了使用游标。(我就是不想使用游标故有此一问)
4.sql的排序允许对排序列进行计算。
*/
结贴。
create table t(id datetime)
insert into t
select '2010-9-3'
union all
select '2010-9-1'
union all
select '2010-9-5'
union all
select '2010-9-22'
go
select * from (
select top 1 * from t where id<'2010-9-3' order by id) a
union all
select * from (
select top 1 * from t where id>'2010-9-3' order by id) b
/*
id
-----------------------
2010-09-01 00:00:00.000
2010-09-05 00:00:00.000
(2 行受影响)
*/
create table t(id date);
insert into t
select '2010-9-3'
union all
select '2010-9-1'
union all
select '2010-9-5'
union all
select '2010-9-22'
go
drop table t
select a.ID,b.ID as 最近
from t as a
Cross apply
(select *,DENSE=DENSE_RANK()over(order by abs(DATEDIFF(D,a.ID,ID)) ) from t where ID<>a.ID ) as b
where b.DENSE=1
order by 1
/*
ID 最近
2010-09-01 2010-09-03
2010-09-03 2010-09-01
2010-09-03 2010-09-05
2010-09-05 2010-09-03
2010-09-22 2010-09-05
*/
create table t(id datetime);
insert into t
select '2010-9-3'
union all
select '2010-9-1'
union all
select '2010-9-5'
union all
select '2010-9-22';
go
select id,(select top 1 id from t where id<>a.id order by abs(datediff(d,id,a.id)))as latestid
from t a
/*
id latestid
----------------------- -----------------------
2010-09-03 00:00:00.000 2010-09-05 00:00:00.000
2010-09-01 00:00:00.000 2010-09-03 00:00:00.000
2010-09-05 00:00:00.000 2010-09-03 00:00:00.000
2010-09-22 00:00:00.000 2010-09-05 00:00:00.000
(4 行受影响)
*/
go
drop table t
create table t(id datetime);
insert into t
select '2010-9-3'
union all
select '2010-9-1'
union all
select '2010-9-5'
union all
select '2010-9-22';
go
declare @dt datetime
set @dt='2010-9-3'
select top 1 * from t
where id<>@dt
order by abs(datediff(d,id,@dt))
/*
id
-----------------------
2010-09-05 00:00:00.000
(1 行受影响)
*/
go
drop table t
select top 1 * from t order by abs(datediff(dd,id,'2010-09-03'))