根据日期查询不同结果的问题
表1

code date_time other
0002 2012-2-2 A
0002 2013-3-8 B
0009 2012-2-3 C
0009 2012-2-9 D


表1

code date_time1 other1
0001 2013-2-1 s
0002 2013-2-2 D
0002 2013-2-3 D
0002 2013-3-10 D

当日期是：2013-2-2时候

查询如下：

code date_time date_time1 other other1
0002 2012-2-2 2013-2-2 A s

当日期是：2013-2-3时候
查询如下：

code date_time date_time1 other other1
无记录

当日期是：2013-3-10时候
查询如下：
code date_time date_time1 other other1
0002 2013-3-8 2012-3-10 C D

等在查询2013-2-2时候
查询结果是：
code date_time date_time1 other other1
0002 2012-2-2 2013-2-2 A s













[解决办法]

----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2013-02-21 10:14:05
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--Apr 22 2011 11:57:00 
--Copyright (c) Microsoft Corporation
--Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([code] varchar(4),[date_time] datetime,[other] varchar(1))
insert [a]
select '0002','2012-2-2','A' union all
select '0002','2013-3-8','B' union all
select '0009','2012-2-3','C' union all
select '0009','2012-2-9','D'
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([code] varchar(4),[date_time1] datetime,[other1] varchar(1))
insert [b]
select '0001','2013-2-1','s' union all
select '0002','2013-2-2','D' union all
select '0002','2013-2-3','D' union all
select '0002','2013-3-10','D'
--------------开始查询--------------------------
if OBJECT_ID('test') is not null
drop proc test 
 
go
create proc test
(
@time datetime
)
as
begin
select
   a.code,a.date_time,b.date_time1,a.other,b.other1
from
a ,b
where
a.code=b.code
and
a.date_time=@time
and
    abs(datediff(dd,date_time1,@time)) =(select min(abs(datediff(dd,date_time1,@time))) from b where a.code=b.code)
end
go
exec test '2012-2-2'
go
----------------结果----------------------------
/* 
(4 行受影响)

(4 行受影响)
code date_time               date_time1              other other1
---- ----------------------- ----------------------- ----- ------
0002 2012-02-02 00:00:00.000 2013-02-02 00:00:00.000 A     D

(1 行受影响)


*/