调价表
价格 时间
100 2009-10-1
200 2009-10-10
300 2009-11-1
如何判断日期对应正确价格,
如,取到一个日期变量,如2009-10月5日,怎样取到2009-10-1对应到得价格100,高手请指点下!
价格 时间
100 2009-10-1
200 2009-10-10
300 2009-11-1
如何判断日期对应正确价格,
如,取到一个日期变量,如2009-10月5日,怎样取到2009-10-1对应到得价格100,高手请指点下!
insert into tb values(100 , '2009-10-1')
insert into tb values(200 , '2009-10-10')
insert into tb values(300 , '2009-11-1')
goselect top 1 * from tb where 时间 < '2009-10-05' order by 时间 descdrop table tb /*价格 时间
----------- ------------------------------------------------------
100 2009-10-01 00:00:00.000(所影响的行数为 1 行)*/
if object_id('tb') is not null drop table tb
go
create table tb([price] INT,[time] varchar(50))
insert into tb
select 100,'2009-10-1' union all
select 200,'2009-10-10' union all
select 300,'2009-11-1'
go
select top 1 * from tb
order by abs(datediff(day,time,'2009-10-5'))
/*
price time
----------- --------------------------------------------------
100 2009-10-1
*/select top 1 * from tb
order by abs(datediff(day,time,'2009-10-7'))
/*
price time
----------- --------------------------------------------------
200 2009-10-10
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-23 22:59:30
-- 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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([价格] int,[时间] datetime)
insert [tb]
select 100,'2009-10-1' union all
select 200,'2009-10-10' union all
select 300,'2009-11-1'
--------------开始查询--------------------------
declare @s datetime
set @s='2009-10-5'
select top 1 * from [tb] order by abs(datediff(dd,@s,时间))
----------------结果----------------------------
/* 价格 时间
----------- -----------------------
100 2009-10-01 00:00:00.000(1 行受影响)
*/
insert into tb values(100 , '2009-10-1')
insert into tb values(200 , '2009-10-10')
insert into tb values(300 , '2009-11-1')
goselect top 1 * from tb where 时间 < '2009-10-05' order by 时间 descselect top 1 * from tb order by abs(datediff(day ,时间, '2009-10-05'))drop table tb /*
价格 时间
----------- ------------------------------------------------------
100 2009-10-01 00:00:00.000(所影响的行数为 1 行)
*/