表t1AA BBB CCC
2010-8-1 10:11:11 CCC DDD
2010-8-3 10:11:11 GGG TTT
2010-8-5 10:11:11 WWW QQQ
2010-8-9 10:11:11 BBB RRR
查询2010-8-3的最近日期的数据,假如有两条2010-8-3的数据,则取时间最大的数据;如果没有2010-8-3之后的数据,则取2010-8-3之前的数据,否则取2010-8-3前的数据,查询结果倒序显示。查询结果:
AA BBB CCC
2010-8-5 WWW QQQ
2010-8-1 CCC DDD请指教。
2010-8-1 10:11:11 CCC DDD
2010-8-3 10:11:11 GGG TTT
2010-8-5 10:11:11 WWW QQQ
2010-8-9 10:11:11 BBB RRR
查询2010-8-3的最近日期的数据,假如有两条2010-8-3的数据,则取时间最大的数据;如果没有2010-8-3之后的数据,则取2010-8-3之前的数据,否则取2010-8-3前的数据,查询结果倒序显示。查询结果:
AA BBB CCC
2010-8-5 WWW QQQ
2010-8-1 CCC DDD请指教。
union all
select top 1 * from t1 where datediff(dd,'2010-8-3',aa)<0 order by aa desc
AA BBB CCC
2010-8-1 10:11:11 CCC DDD
2010-8-3 10:11:11 GGG TTT
2010-8-5 10:11:11 WWW QQQ
2010-8-9 10:11:11 BBB RRR结果:
2010-8-5 10:11:11 WWW QQQ
2010-8-3 10:11:11 GGG TTT
2010-8-1 10:11:11 CCC DDD举例2:
AA BBB CCC
2010-8-1 10:11:11 CCC DDD
2010-8-3 10:11:11 GGG TTT
结果:
2010-8-1 10:11:11 CCC DDD举例3:
AA BBB CCC
2010-8-1 10:11:11 CCC DDD
2010-8-3 10:11:11 GGG TTT
2010-8-3 11:11:11 WWW QQQ
2010-8-9 10:11:11 BBB RRR结果:
2010-8-3 11:11:11 WWW QQQ
2010-8-3 10:11:11 GGG TTT
2010-8-1 10:11:11 CCC DDD
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([AA] datetime,[BBB] varchar(3),[CCC] varchar(3))
insert [t1]
select '2010-8-1 10:11:11','CCC','DDD' union all
select '2010-8-3 10:11:11','GGG','TTT' union all
select '2010-8-5 10:11:11','WWW','QQQ' union all
select '2010-8-9 10:11:11','BBB','RRR'
---查询---
select convert(varchar(10),aa,120) as AA,BBB,CCC from(select top 1 * from t1 where datediff(dd,'2010-8-3',aa)>0 order by aa) a
union all
select convert(varchar(10),aa,120) as AA,BBB,CCC from(select top 1 * from t1 where datediff(dd,'2010-8-3',aa)<0 order by aa desc) b---结果---
AA BBB CCC
---------- ---- ----
2010-08-05 WWW QQQ
2010-08-01 CCC DDD(2 行受影响)
select max(AA) from 表t1 where AA<'2010-8-3'
UNION ALL
select min(AA) from 表t1 where AA>'2010-8-3'
---测试数据---
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([AA] datetime,[BBB] varchar(3),[CCC] varchar(3))
insert [t1]
select '2010-8-1 10:11:11','CCC','DDD' union all
select '2010-8-3 10:11:11','GGG','TTT' union all
select '2010-8-5 10:11:11','WWW','QQQ' union all
select '2010-8-9 10:11:11','BBB','RRR'
---查询---
select * from(select top 1 * from t1 where datediff(dd,'2010-8-3',aa)>0 order by aa) a
union all
select * from(select top 1 * from t1 where datediff(dd,'2010-8-3',aa)=0 order by aa) a
union all
select * from(select top 1 * from t1 where datediff(dd,'2010-8-3',aa)<0 order by aa desc) b---结果---
AA BBB CCC
----------------------- ---- ----
2010-08-05 10:11:11.000 WWW QQQ
2010-08-03 10:11:11.000 GGG TTT
2010-08-01 10:11:11.000 CCC DDD(3 行受影响)