现在有三个字段:
NO ITEM_NO START_DATE END_DATE
1 61151011 2008-04-01 2008-11-11
2 61151011 2008-07-20 2008-12-11
3 61151011 2008-08-11 2009-02-11
4 81FM2001 2008-03-15 2008-08-11
5 81FM2001 2008-07-19 2008-07-11
6 81FM2001 2008-05-05 2008-08-11当输入2008-07-21时候,查询的结果NO是 2和5
当输入2008-04-05时候,查询的结果NO是 1和4
就是说找出输入日期与START_DATE的日期最接近的那条记录
请教各位大虾!谢谢!!!
NO ITEM_NO START_DATE END_DATE
1 61151011 2008-04-01 2008-11-11
2 61151011 2008-07-20 2008-12-11
3 61151011 2008-08-11 2009-02-11
4 81FM2001 2008-03-15 2008-08-11
5 81FM2001 2008-07-19 2008-07-11
6 81FM2001 2008-05-05 2008-08-11当输入2008-07-21时候,查询的结果NO是 2和5
当输入2008-04-05时候,查询的结果NO是 1和4
就是说找出输入日期与START_DATE的日期最接近的那条记录
请教各位大虾!谢谢!!!
你这明明是两条纪录嘛
select
from
(
select *
from a
where START_DATE <=to_date(:indate,'yyyy-mm-dd')
order by start_date desc
)
where rownum<=2这是取最接近输入日期的两条,如果是一条就改成rownum<=1
select no from t where START_DATE =(select max(START_DATE) from t where START_DATE<to_date('2008-09-07','yyyy-mm-dd'))
具体修改后面的to_date部分就可以
NO ITEM_NO START_DATE END_DATE
1 61151011 2008-04-01 2008-11-11
2 61151011 2008-07-20 2008-12-11
3 61151011 2008-08-11 2009-02-11 4 81FM2001 2008-03-15 2008-08-11
5 81FM2001 2008-07-19 2008-07-11
6 81FM2001 2008-05-05 2008-08-11 7 91FM2001 2008-03-15 2008-08-11
8 91FM2001 2008-07-19 2008-07-11
9 91FM2001 2008-05-05 2008-08-1110 21FM2001 2008-03-15 2008-08-11
11 21FM2001 2008-07-19 2008-07-11
12 21FM2001 2008-05-05 2008-08-11
from a
where START_DATE <=to_date(:indate,'yyyy-mm-dd')
order by start_date desc
SQL> SELECT *
2 FROM (SELECT TT.*,
3 ROW_NUMBER() OVER(PARTITION BY 物品 ORDER BY(2.15 - 开始日期)) RN
4 FROM TABLE_NAME TT
5 WHERE 开始日期 <= 2.15
6 AND 结束日期 >= 2.15) ZZ
7 WHERE RN = 1;物品 开始日期 结束日期 RN
---- ---------- ---------- ----------
大米 2.1 5.31 1
小麦 1.15 4.31 1
else max(开始日期)
end 开始日期
from a
group by 物品