A,B表左连接后,日期相减得到的绝对值最小的就是你要的...大致如下: select B_JH , B_RQ , B_RCY from( SELECT A_JH , ABS(A_RQ-B_RQ) AS CNT ,B_JH , B_RQ , B_RCY FROM A,B WHERE A.A_JH=B.B_JH (+) order by CNT ) where rownum=1 group by B_JH , B_RQ , B_RCY
SELECT A_JH,A_RQ,(SELECT B_RCY FROM ( SELECT B_JH,B_RCY,ROW_NUMBER() OVER(PARTITION BY B_JH ORDER BY abs(B_RQ-A_RQ) ASC) RN FROM B WHERE B_JH=A_JH) WHERE RN=1) RCY FROM A
SELECT B_JH,B_RQ,B_RCY from( SELECT B_JH,B_RCY,B_RQ, ROW_NUMBER() OVER(PARTITION BY B_JH ORDER BY (B_RQ-A_RQ) ASC) RN FROM B,A WHERE B_JH=A_JH ) WHERE RN=1
不能用group by 分组那样的话我要写好多字段~这只是其中的一部分字段而且那样查出来的数据部准的
with a as( select 'BJ12' A_JH, to_date('2010/07/20', 'yyyy/mm/dd') A_RQ from dual union all select 'SH10', to_date('2010/07/20', 'yyyy/mm/dd') from dual union all select 'TJ20', to_date('2010/07/23', 'yyyy/mm/dd') from dual) ,b as( select 'BJ12' B_JH,to_date( '2010/07/18', 'yyyy/mm/dd') B_RQ, 200 B_RCY from dual union all select 'BJ12',to_date( '2010/07/21', 'yyyy/mm/dd'), 300 from dual union all select 'BJ12',to_date( '2010/07/22', 'yyyy/mm/dd'), 100 from dual union all select 'BJ12',to_date( '2010/07/23', 'yyyy/mm/dd'), 300 from dual union all select 'SH10',to_date( '2010/07/20', 'yyyy/mm/dd'), 150 from dual union all select 'SH10',to_date( '2010/07/22', 'yyyy/mm/dd'), 150 from dual union all select 'TJ20',to_date( '2010/07/23', 'yyyy/mm/dd'), 200 from dual) select a_jh, b_rq, B_RCY from (select t.*, row_number() over(partition by a_jh order by sa) rn from (select a_jh, a_rq, b_jh, b_rq, B_RCY, abs(b_rq - a_rq) sa from b, a where a.a_jh = b.b_jh) t) where rn = 1
相减有负值吧,order 的时候就不准确了select a.A_JH,a.A_RQ, b.B_RQ, row_number()over(partition by a.A_JH order by abs(b.B_RQ-a.A_RQ)) from a,b where a.A_JH = b.B_JH
select a_jh, b_rq, B_RCY from (select a.*, b.*, row_number() over(partition by a_jh order by abs(b_rq - a_rq)) rn from b, a where a.a_jh = b.b_jh) t where rn = 1
select B_JH , B_RQ , B_RCY
from(
SELECT A_JH , ABS(A_RQ-B_RQ) AS CNT ,B_JH , B_RQ , B_RCY
FROM A,B
WHERE A.A_JH=B.B_JH (+)
order by CNT
)
where rownum=1
group by B_JH , B_RQ , B_RCY
SELECT B_JH,B_RCY,ROW_NUMBER() OVER(PARTITION BY B_JH ORDER BY abs(B_RQ-A_RQ) ASC) RN
FROM B WHERE B_JH=A_JH)
WHERE RN=1) RCY FROM A
SELECT B_JH,B_RCY,B_RQ,
ROW_NUMBER() OVER(PARTITION BY B_JH ORDER BY (B_RQ-A_RQ) ASC) RN FROM B,A
WHERE B_JH=A_JH
) WHERE RN=1
with a as(
select 'BJ12' A_JH, to_date('2010/07/20', 'yyyy/mm/dd') A_RQ from dual union all
select 'SH10', to_date('2010/07/20', 'yyyy/mm/dd') from dual union all
select 'TJ20', to_date('2010/07/23', 'yyyy/mm/dd') from dual)
,b as(
select 'BJ12' B_JH,to_date( '2010/07/18', 'yyyy/mm/dd') B_RQ, 200 B_RCY from dual union all
select 'BJ12',to_date( '2010/07/21', 'yyyy/mm/dd'), 300 from dual union all
select 'BJ12',to_date( '2010/07/22', 'yyyy/mm/dd'), 100 from dual union all
select 'BJ12',to_date( '2010/07/23', 'yyyy/mm/dd'), 300 from dual union all
select 'SH10',to_date( '2010/07/20', 'yyyy/mm/dd'), 150 from dual union all
select 'SH10',to_date( '2010/07/22', 'yyyy/mm/dd'), 150 from dual union all
select 'TJ20',to_date( '2010/07/23', 'yyyy/mm/dd'), 200 from dual)
select a_jh, b_rq, B_RCY
from (select t.*, row_number() over(partition by a_jh order by sa) rn
from (select a_jh, a_rq, b_jh, b_rq, B_RCY, abs(b_rq - a_rq) sa
from b, a
where a.a_jh = b.b_jh) t)
where rn = 1
select a_jh, b_rq, B_RCY
from (select a.*,
b.*,
row_number() over(partition by a_jh order by abs(b_rq - a_rq)) rn
from b, a
where a.a_jh = b.b_jh) t
where rn = 1
如果b的数据量很大,b和a放在一起会不会影响速度啊1·能优化一下吗??