价格表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
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
set @dt = '2008-12-11'select top 1 * from en_price where date <= @dt order by date desc
from En_Price
order by abs(datediff(d,'???',date) )
-- 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 行受影响)
*/
-- 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]) ascset @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)*/
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')
godeclare @dt as datetimeset @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
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
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_Priceif @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