在一个表中查询最近日期？？？？

shuihan16 2009-12-10 05:43:26

table1表中记录如下：
A B C D
001 1.0 2009/01/03 aaaa
001 0.9 2008/09/03 aaaa
001 1.1 2008/08/03 aaaa
002 1.2 2009/01/03 aaaa
002 0.8 2009/01/03 aaaa

比如我选择的日期是“2008/09/09”，我在table1表中想查找 C列中与这个日期最近的那一条记录，怎么写这条SQL语句呢？

...全文

128 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

jinjazzli 2009-12-10

打赏
举报

2个日期相减，再取绝对值，排序，取最小的，就是最近的

shuihan16 2009-12-10

打赏
举报

[Quote=引用 2 楼 jiangshun 的回复:]
--> 测试时间：2009-12-10 17:50:31
--> 测试菜鸟：l8r
--> 我的淘宝：《戒色坊》http://shop36766744.taobao.com/

SQL codeifobject_id('[TB]')isnotnulldroptable[TB]createtable[TB]([A]varchar(3),[B] numeric(2,1),[C]datetime,[D]varchar(4))insert[TB]select'001',1.0,'2009/01/03','aaaa'unionallselect'001',0.9,'2008/09/03','aaaa'unionallselect'001',1.1,'2008/08/03','aaaa'unionallselect'002',1.2,'2009/01/03','aaaa'unionallselect'002',0.8,'2009/01/03','aaaa'declare@dtdatetimeset@dt='2008/09/09'selecttop1*from[TB]orderbyabs(datediff(dd,@dt,C))/*
A B C D
---- ---- ------------------------------------------------------ ----
001 .9 2008-09-03 00:00:00.000 aaaa

（所影响的行数为 1 行）*/droptable[TB]

[/Quote]

order by abs(datediff(dd,@dt,C))

这一段是什么意思呀？ abs???

ACMAIN_CHM 2009-12-10

打赏
举报

select *

from table1

where C=(select max(C) from table1 where C<='2008/09/09')

dawugui 2009-12-10

打赏
举报

--如果只存在一条记录。

create table tb(A varchar(10), B decimal(18,1), C datetime, D varchar(10))

insert into tb values('001' , 1.0 , '2009/01/03' , 'aaaa') 

insert into tb values('001' , 0.9 , '2008/09/03' , 'aaaa') 

insert into tb values('001' , 1.1 , '2008/08/03' , 'aaaa') 

insert into tb values('002' , 1.2 , '2009/01/03' , 'aaaa') 

insert into tb values('002' , 0.8 , '2009/01/03' , 'aaaa')

go



select top 1 * from tb order by abs(datediff(day,c,'2008/09/09'))



drop table tb 



/*

A          B                    C                                                      D          

---------- -------------------- ------------------------------------------------------ ---------- 

001        .9                   2008-09-03 00:00:00.000                                aaaa

*/

create table tb(A varchar(10), B decimal(18,1), C datetime, D varchar(10))

insert into tb values('001' , 1.0 , '2009/01/03' , 'aaaa') 

insert into tb values('001' , 0.9 , '2008/09/03' , 'aaaa') 

insert into tb values('001' , 1.1 , '2008/08/03' , 'aaaa') 

insert into tb values('002' , 1.2 , '2009/01/03' , 'aaaa') 

insert into tb values('002' , 0.8 , '2009/01/03' , 'aaaa')

insert into tb values('001' , 1.0 , '2008/09/10' , 'aaaa') --加条测试语句

go



select top 1 * from tb order by abs(datediff(day,c,'2008/09/09'))



drop table tb 



/*

A          B                    C                                                      D          

---------- -------------------- ------------------------------------------------------ ---------- 

001        1.0                  2008-09-10 00:00:00.000                                aaaa



（所影响的行数为 1 行）

*/

--如果存在多条记录

create table tb(A varchar(10), B decimal(18,1), C datetime, D varchar(10))

insert into tb values('001' , 1.0 , '2009/01/03' , 'aaaa') 

insert into tb values('001' , 0.9 , '2008/09/03' , 'aaaa') 

insert into tb values('001' , 1.1 , '2008/08/03' , 'aaaa') 

insert into tb values('002' , 1.2 , '2009/01/03' , 'aaaa') 

insert into tb values('002' , 0.8 , '2009/01/03' , 'aaaa')

insert into tb values('002' , 1.2 , '2008/09/08' , 'aaaa') --增加测试数据

insert into tb values('002' , 0.8 , '2008/09/10' , 'aaaa') --增加测试数据

go



select * from tb where abs(datediff(day,c,'2008/09/09')) = (select min(abs(datediff(day,c,'2008/09/09'))) from tb)



drop table tb 



/*

A          B                    C                                                      D          

---------- -------------------- ------------------------------------------------------ ---------- 

002        1.2                  2008-09-08 00:00:00.000                                aaaa

002        .8                   2008-09-10 00:00:00.000                                aaaa



（所影响的行数为 2 行）



*/

--小F-- 2009-12-10

打赏
举报

[Quote=引用 5 楼 shuihan16 的回复:]
引用 1 楼 fredrickhu 的回复:
SQL codeselecttop1*from tborderby cdesc

这样是最大的日期，我选择的日期的最近日期也有可能不是最大日期呀
[/Quote]

select top 1 * from tb order by abs(datediff(day,c,'2008/09/09'))

shuihan16 2009-12-10

打赏
举报

[Quote=引用 1 楼 fredrickhu 的回复:]
SQL codeselecttop1*from tborderby cdesc
[/Quote]

这样是最大的日期，我选择的日期的最近日期也有可能不是最大日期呀

dawugui 2009-12-10

打赏
举报

--如果只存在一条记录。



select top 1 * from tb order by abs(datediff(day,c,'2008/09/09'))



--如果存在多条记录



select * from tb where abs(datediff(day,c,'2008/09/09')) =  (select top 1 * from tb order by abs(datediff(day,c,'2008/09/09')))

bancxc 2009-12-10

打赏
举报

select top * from tb 

where c<'2008/09/09'

order by c desc

jiangshun 2009-12-10

打赏
举报

--> 测试时间：2009-12-10 17:50:31
--> 测试菜鸟：l8r
--> 我的淘宝：《戒色坊》http://shop36766744.taobao.com/





if object_id('[TB]') is not null drop table [TB]

create table [TB]([A] varchar(3),[B] numeric(2,1),[C] datetime,[D] varchar(4))

insert [TB]

select '001',1.0,'2009/01/03','aaaa' union all

select '001',0.9,'2008/09/03','aaaa' union all

select '001',1.1,'2008/08/03','aaaa' union all

select '002',1.2,'2009/01/03','aaaa' union all

select '002',0.8,'2009/01/03','aaaa'



declare @dt datetime

set @dt='2008/09/09'

select top 1 * from [TB]

order by abs(datediff(dd,@dt,C)) 





/*

A    B    C                                                      D    

---- ---- ------------------------------------------------------ ---- 

001  .9   2008-09-03 00:00:00.000                                aaaa



（所影响的行数为 1 行）



*/



drop table [TB]