根据日期查询不同结果的问题
表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 行受影响)
*/
