查询最近的日期数据

[xredhat]

表t1

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-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

请指教。

[百年树人]

select top 1 * from t1 where datediff(dd,'2010-8-3',aa)>0 order by aa
union all
select top 1 * from t1 where datediff(dd,'2010-8-3',aa)<0 order by aa desc

[xredhat]

举例1：
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 行受影响)

[duanzhi1984]

select max(AA) from 表t1 where AA<'2010-8-3'
UNION ALL
select min(AA) from 表t1 where AA>'2010-8-3'

[duanzhi1984]

就是找大于2010-08-03 的最小值 及小于2010-08-03的最大值

[百年树人]

--增加显示查询日期的数据

---测试数据---
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 行受影响)

[xredhat]

[Quote=引用 6 楼 josy 的回复:]
--增加显示查询日期的数据

SQL code
---测试数据---
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'……
[/Quote]

谢谢 搞定