新手讨教:日期判断问题? (今天已提过问,但条件没说全。现再次请教)

[jx_liu]

在字段1中有很多相同的记录,如何找出与指定日期最接近的记录?如下:
表中
日期 字段1 字段2 ....
2007-10-01 a 1
2007-12-10 r 3
2007-12-20 r 2
2007-12-10 z 7
2007-12-16 z 6
2007-12-26 z 8
.......... . .

若指定日期为"2007-12-17"则得到
2007-10-01 a 1
2007-12-10 r 3
2007-12-16 z 6

若指定日期为"2007-12-30"则得到
2007-10-01 a 1
2007-12-20 r 2
2007-12-26 z 8
谢谢!

[-狙击手-]

select *
from tablename a
where not exists(select 1 from tablename where a.字段1 = 字段1 and a.日期 < 日期)
and 日期 < '2007-12-17'


select *
from tablename a
where not exists(select 1 from tablename where a.字段1 = 字段1 and a.日期 < 日期)
and 日期 < '2007-12-30'

[jx_liu]

无枪狙击手:
谢谢你再次指教,试过但字段1中重复记录未取出

[dawugui]

select a.* from tb a,
(
  select 字段1 , min(时间差) 时间差 from
  (
    select abs(datediff(day,@dt,日期)) 时间差,日期,字段1 from tb
  ) t group by 字段1
) m
where a.字段1 = m.字段1 and abs(datediff(day,@dt,a.日期)) = b.时间差

[-狙击手-]

什么意思？

[dawugui]

create table tb(日期 datetime, 字段1 varchar(10), 字段2 int)
insert into tb values('2007-10-01', 'a', 1 )
insert into tb values('2007-12-10', 'r', 3 ) 
insert into tb values('2007-12-20', 'r', 2 ) 
insert into tb values('2007-12-10', 'z', 7 )
insert into tb values('2007-12-16', 'z', 6 ) 
insert into tb values('2007-12-26', 'z', 8 )
go

declare @dt as datetime
set @dt = '2007-12-17'

select a.* from tb a,
(
  select 字段1 , min(时间差) 时间差 from
  (
    select abs(datediff(day,@dt,日期)) 时间差,日期,字段1 from tb
  ) t group by 字段1
) m
where a.字段1 = m.字段1 and abs(datediff(day,@dt,a.日期)) = m.时间差
/*
日期                      字段1        字段2
----------------------- ---------- -----------
2007-10-01 00:00:00.000 a          1
2007-12-20 00:00:00.000 r          2
2007-12-16 00:00:00.000 z          6

(3 行受影响)
*/

set @dt = '2007-12-30'
select a.* from tb a,
(
  select 字段1 , min(时间差) 时间差 from
  (
    select abs(datediff(day,@dt,日期)) 时间差,日期,字段1 from tb
  ) t group by 字段1
) m
where a.字段1 = m.字段1 and abs(datediff(day,@dt,a.日期)) = m.时间差
/*
日期                      字段1        字段2
----------------------- ---------- -----------
2007-10-01 00:00:00.000 a          1
2007-12-20 00:00:00.000 r          2
2007-12-26 00:00:00.000 z          8

(3 行受影响)
*/

drop table tb

[-狙击手-]

不好意思，失误了

declare @t table(日期 datetime, 字段1 varchar(2),字段2 varchar(2))
insert @t select '2007-10-01','a','1'   
insert @t select '2007-12-10','r','3'       
insert @t select '2007-12-20','r','2'   
insert @t select '2007-12-10','z','7' 
insert @t select '2007-12-16','z','6'   
insert @t select '2007-12-26','z','8'

select *
from @t a
where not exists(select 1 from @t where a.字段1 = 字段1 and a.日期 < 日期 and 日期 < '2007-12-17')
and 日期 < '2007-12-17'


select *
from @t a
where not exists(select 1 from @t where a.字段1 = 字段1 and a.日期 < 日期 and 日期 < '2007-12-30' )
and 日期 < '2007-12-30' 

/*


日期                                                     字段1  字段2  
------------------------------------------------------ ---- ---- 
2007-10-01 00:00:00.000                                a    1
2007-12-10 00:00:00.000                                r    3
2007-12-16 00:00:00.000                                z    6

（所影响的行数为 3 行）

日期                                                     字段1  字段2  
------------------------------------------------------ ---- ---- 
2007-10-01 00:00:00.000                                a    1
2007-12-20 00:00:00.000                                r    2
2007-12-26 00:00:00.000                                z    8

（所影响的行数为 3 行）
*/

[-狙击手-]

龟，你的那个17号日期结果不对

[dawugui]

--楼主描述有错,不是求最接近的日期,而是求小于给定日期的最大日期.前者如上所写.后者如下:
create table tb(日期 datetime, 字段1 varchar(10), 字段2 int)
insert into tb values('2007-10-01', 'a', 1 )
insert into tb values('2007-12-10', 'r', 3 ) 
insert into tb values('2007-12-20', 'r', 2 ) 
insert into tb values('2007-12-10', 'z', 7 )
insert into tb values('2007-12-16', 'z', 6 ) 
insert into tb values('2007-12-26', 'z', 8 )
go

declare @dt as datetime
set @dt = '2007-12-17'

select a.* from tb a,
(
  select max(日期) 日期,字段1 from tb where 日期 < @dt group by 字段1
) t
where a.字段1 = t.字段1 and a.日期 = t.日期 
order by a.字段1
/*
日期                      字段1        字段2
----------------------- ---------- -----------
2007-10-01 00:00:00.000 a          1
2007-12-10 00:00:00.000 r          3
2007-12-16 00:00:00.000 z          6

(3 行受影响)
*/

set @dt = '2007-12-30'
select a.* from tb a,
(
  select max(日期) 日期,字段1 from tb where 日期 < @dt group by 字段1
) t
where a.字段1 = t.字段1 and a.日期 = t.日期 
order by a.字段1
/*
日期                      字段1        字段2
----------------------- ---------- -----------
2007-10-01 00:00:00.000 a          1
2007-12-20 00:00:00.000 r          2
2007-12-26 00:00:00.000 z          8

(3 行受影响)
*/

drop table tb

[dawugui]

龟，你的那个17号日期结果不对

不是我不对,是他描述不对.不是求最近,是求小于给定日期的最大日期.

[jx_liu]

若字段1中记录相同,则取与指定日期最接近(或者说:在小于等于指定日期中选日期最大的那条记录)的记录,若字段1中记录不同,则取小于等于指定日期。如
若指定日期为"2007-12-17"则
字段1中记录为"a"仅1条,且日期小于"2007-12-17"则取该记录: 2007-10-01 a 1
字段1中记录为"r"仅2条,则取日期小于"2007-12-17"中日期最大的记录: 2007-12-10 r 3
字段1中记录为"z"仅3条,则取日期小于"2007-12-17"中日期最大的记录: 2007-12-16 z 6
结果:
2007-10-01 a 1
2007-12-10 r 3
2007-12-16 z 6

[jx_liu]

潇洒老乌龟说的对,是我描述不对。!抱歉

[dawugui]

上面已经有正确结果,具体哪个是你需要的,你自己选.

[jx_liu]

可以了,谢谢2位!