table Acolumn c g end_date
1 2 04/01/2009
1 3 05/01/2009
1 4 06/01/2009
求出c=1中的end_date与04/30/2009最近的记录的g 即要找出 g=2的记录select g from A where c = 1 and end_date = (select a1.end_date from A as a1 where ......后面就没能想出来啊)
1 2 04/01/2009
1 3 05/01/2009
1 4 06/01/2009
求出c=1中的end_date与04/30/2009最近的记录的g 即要找出 g=2的记录select g from A where c = 1 and end_date = (select a1.end_date from A as a1 where ......后面就没能想出来啊)
where end_date=(
select end_date from (
select end_date,abs(end_date-to_date('20090430','yyyymmdd')) rn
from t
order by rn)
where rownum=1)
where not exists(
select 1 from a
where abs((end_date-date'2009-4-30')/(t.end_date-date'2009-4-30'))<1)2.select * from(
select a.*,dense_rank(order by abs(end_date-date'2009-4-30'))dk
from a)
where dk=1
FROM a
ORDER BY ABS(DATEDIFF(S,'2009-04-30',End_date))
SELECT TOP 1 c,g,end_date
FROM a
ORDER BY ABS(DATEDIFF(S,'2009-04-30',End_date))
where end_date=(
select end_date from (
select end_date,abs(end_date-to_date('20090430','yyyymmdd')) rn
from t
order by rn)
where rownum=1)
这个挺通俗的哈~~
SELECT t.* FROM
( SELECT c,g,end_date
FROM a
ORDER BY ABS(end_date-TO_DATE('2009-04-30 10:20:30','YYYY-MM-DD HH24:MI:SS'))
) t
WHERE rownum=1;
--只查一个end_date与04/30/2009最近的记录,即如果最接近的纪录有多个相同的也只要一个
select * from
( select c,g,end_date,row_number() over(order by end_date-date'2009-4-30') dd from a)
where dd=1;
--查出所有end_date与04/30/2009最近的记录,即如果最接近的纪录有多个相同的一起查出来
select * from
( select c,g,end_date,rank() over(order by end_date-date'2009-4-30') dd from a)
where dd=1;
或者是
select * from
( select c,g,end_date,dense_rank() over(order by end_date-date'2009-4-30') dd from a)
where dd=1;
看哪种是你想要的。