遇到个查询单价的问题

[Jgood_boy]

价格表En_Price: ID 、 Price（单价）、 Date（价格更新日期）

1 20 2009-01-02
2 23 2009-02-01
3 24 2009-02-15
4 20 2009-03-08
5 32 2009-08-11

例如
查询 2008-12-11 显示 1 20 2009-01-02
查询 2009-01-02 显示 1 20 2009-01-02
查询 2009-01-05 显示 1 20 2009-01-02
查询 2009-02-05 显示 2 23 2009-02-01
查询 2009-02-25 显示 3 24 2009-02-15
查询 2009-11-25 显示 5 32 2009-08-11

[dawugui]

declare @dt as datetime
set @dt = '2008-12-11'

select top 1 * from en_price where date <= @dt order by date desc

[-狙击手-]

select top 1 *
from En_Price
order by abs(datediff(d,'???',date) )

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-24 17:10:22
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ID] int,[Price] int,[Date] datetime)
insert [tb]
select 1,20,'2009-01-02' union all
select 2,23,'2009-02-01' union all
select 3,24,'2009-02-15' union all
select 4,20,'2009-03-08' union all
select 5,32,'2009-08-11'
--------------开始查询--------------------------
declare @dt as datetime
set @dt = '2009-01-05'

select top 1 * from tb order by  abs(datediff(d,@dt,date) )
----------------结果----------------------------
/*ID          Price       Date
----------- ----------- -----------------------
1           20          2009-01-02 00:00:00.000

(1 行受影响) 
*/

[icelovey]

-- =============================================
-- Author:      T.O.P
-- Create date: 2009/11/24
-- Version:     SQL SERVER 2005
-- =============================================
declare @TB table([ID] int,[Price] int,[Date] datetime)
insert @TB
select 1,20,'2009-01-02' union all
select 2,23,'2009-02-01' union all
select 3,24,'2009-02-15' union all
select 4,20,'2009-03-08' union all
select 5,32,'2009-08-11'

declare @date datetime
set @date = '2008-12-11'
select top 1 *
from @TB
order by  datediff(dd,@date,[Date]) asc

set @date = '2009-11-25'
select top 1 *
from @TB
order by  abs(datediff(dd,@date,[Date])) asc



--测试结果:
/*
(5 row(s) affected)
ID          Price       Date
----------- ----------- -----------------------
1           20          2009-01-02 00:00:00.000

(1 row(s) affected)

ID          Price       Date
----------- ----------- -----------------------
5           32          2009-08-11 00:00:00.000

(1 row(s) affected)

*/

[dawugui]

[Quote=引用 1 楼 dawugui 的回复:]
SQL codedeclare@dtasdatetimeset@dt='2008-12-11'selecttop1*from en_pricewhere date<=@dtorderby datedesc
[/Quote]
理解错了.这个对了.

create table En_Price(ID int,Price int,Date datetime)
insert into En_Price values(1 ,20 ,'2009-01-02') 
insert into En_Price values(2 ,23 ,'2009-02-01') 
insert into En_Price values(3 ,24 ,'2009-02-15 ')
insert into En_Price values(4 ,20 ,'2009-03-08') 
insert into En_Price values(5 ,32 ,'2009-08-11') 
go

declare @dt as datetime

set @dt = '2008-12-11'
select * from En_Price where abs(datediff(dd,date,@dt)) = (select min(abs(datediff(dd,date,@dt))) dt from En_Price)
/*
ID          Price       Date                                                   
----------- ----------- ------------------------------------------------------ 
1           20          2009-01-02 00:00:00.000

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

set @dt = '2009-01-02'
select * from En_Price where abs(datediff(dd,date,@dt)) = (select min(abs(datediff(dd,date,@dt))) dt from En_Price)
/*
ID          Price       Date                                                   
----------- ----------- ------------------------------------------------------ 
1           20          2009-01-02 00:00:00.000

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

drop table En_Price

[nianran520]

--> 测试数据：@table
declare @table table([ID] int,[Price] int,[Date] varchar(10))
insert @table
select 1,20,'2009-01-02' union all
select 2,23,'2009-02-01' union all
select 3,24,'2009-02-15' union all
select 4,20,'2009-03-08' union all
select 5,32,'2009-08-11'

select top 1 ID, Price,Date from 
(select ID, Price,Date,
abs(datediff(day,'2009-02-05',Date)) as 差值
from @table) t 
order by 差值

--结果
------------------
2	23	2009-02-01

[Jgood_boy]

谢谢大家问题解决了，我想得到的是一个日期的价格
declare @En_Price Table(id int identity(1,1),Price int,Date datetime)
insert into @En_Price values(17 ,'2007-11-01')
insert into @En_Price values(15 ,'2008-11-01')
insert into @En_Price values(10 ,'2008-12-01')
insert into @En_Price values(20 ,'2009-01-02')
insert into @En_Price values(23 ,'2009-02-01')
insert into @En_Price values(24 ,'2009-02-15 ')
insert into @En_Price values(20 ,'2009-03-08')
insert into @En_Price values(32 ,'2009-08-11')

declare @DATE datetime
declare @Price int
set @DATE = '2008-12-31'
declare @count int
set @Price = null
select @count=count(*) from @En_Price

if @count=1
begin
select @Price=Price from @En_Price
end
else
begin
select top 1 @Price=Price from @En_Price where convert(varchar(10),Date ,120)<=convert(varchar(10),@DATE ,120) order by abs(datediff(dd,@DATE,Date) ) asc
if @Price is null
select top 1 @Price=Price from @En_Price
end
select @Price
set @DATE = '2007-12-31'
if @count=1
begin
select @Price=Price from @En_Price
end
else
begin
select top 1 @Price=Price from @En_Price where convert(varchar(10),Date ,120)<=convert(varchar(10),@DATE ,120) order by abs(datediff(dd,@DATE,Date) ) asc
if @Price is null
select top 1 @Price=Price from @En_Price
end
select @Price