select t.myid,t.mybh,min(myrq)
from (select abs(b.rq-a.rq) myrq,b.id myid,b.bh mybh
from a,b where a.id=b.id ) t
where t.myid='01';
select t.myid,t.mybh,min(myrq)
from (select abs(b.rq-a.rq) myrq,b.id myid,b.bh mybh
from a,b where a.id=b.id ) t
where t.myid='02';
select t.myid,t.mybh,min(myrq)
from (select abs(b.rq-a.rq) myrq,b.id myid,b.bh mybh
from a,b where a.id=b.id ) t
where t.myid='03';
怎么整合成一句呢?
from (select abs(b.rq-a.rq) myrq,b.id myid,b.bh mybh
from a,b where a.id=b.id ) t
where t.myid='01';
select t.myid,t.mybh,min(myrq)
from (select abs(b.rq-a.rq) myrq,b.id myid,b.bh mybh
from a,b where a.id=b.id ) t
where t.myid='02';
select t.myid,t.mybh,min(myrq)
from (select abs(b.rq-a.rq) myrq,b.id myid,b.bh mybh
from a,b where a.id=b.id ) t
where t.myid='03';
怎么整合成一句呢?
-- ----------
01 2004-01-01
02 2004-01-01
03 2004-01-01SQL> select * from b;ID BH RQ
-- ---- ----------
01 0101 2002-01-01
01 0102 2003-10-01
01 0105 2003-12-12
02 0101 2003-05-01
02 0102 2004-08-09
03 0101 2003-12-30已选择6行。SQL> select b.id,b.bh from a,b where (b.id,abs(b.rq-a.rq)) in (select id,min(rq)
rq from (select b.id,b.bh,abs(b.rq-a.rq) rq from a,b where a.id=b.id) group by
id) and a.id=b.id;ID BH
-- ----
01 0105
02 0102
03 0101
but you can have a try:
select a.id,b.rq
from
(select b.id,b.rq,dense_rank() over( order by abs(a.rq-b.rq)) rank from
a ,b
where
a.id=b.id and dense_rank() over( order by abs(a.rq-b.rq))=1)
from
(select b.id,b.rq,dense_rank() over( order by abs(a.rq-b.rq)) rank from
a ,b
where
a.id=b.id )
where rank=1;
SELECT id
(SELECT TOP 1 hyfl
FROM b
WHERE b.rq <= a.rq AND a.id = b.id
ORDER BY b.rq DESC) AS hylf
FROM a
SQL>
select b.id,b.bh from a,b where (b.id,abs(b.rq-a.rq))
in (select id,min(rq) rq from (select b.id,b.bh,abs(b.rq-a.rq) rq
from a,b where a.id=b.id) group byid)
and a.id=b.id;ID BH
-- ----
01 0105
02 0102
03 0101
当记录很多的时候,效率恐怕就很让人担忧了...
id, rq
0001 2004-6-4
0002 2004-6-4
0003 2004-6-4
0004 2004-6-4
0001 2004-3-1
sql>select * from b
id bh rq
0001 0101 2004-1-3
0001 0102 2004-3-5
0002 0101 2004-1-4
0003 0101 2004-3-5
运行要的结果是
id bh
0001 0102
0002 0101
0003 0101
0004 null
0001 0101
from ta a,tb b where a.id=b.id
and (a.rq-b.rq) =
(
select min(a.rq-c.rq) from tb c where c.id=a.id
)
select a.id,c.bh from a,(select DISTINCT b.id,b.bh from b) c
where a.id=c.id(+)