select a.id,nvl(b.code,'null'),nvl(b.updatetime,'null') from A a left join B b on a.name = b.name;select a.id,nvl(b.code,'null'),nvl(b.updatetime,'null') from A a , B b where a.name = b.name(+);
--先连接,再去重 SELECT * FROM (SELECT a.id, a.name, b.code FROM a LEFT JOIN b ON a.name = b.name) c WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.name = c.name AND b.code < c.code); --先去掉重复,再连接 SELECT a.id, a.name, c.code FROM a LEFT JOIN (SELECT * FROM b WHERE NOT EXISTS (SELECT * FROM b b1 WHERE b.name = b1.name AND b.code > b1.code)) c ON a.name = c.name;
select c.id, c.code, c.updatetime from (select a.id, a.name, b.code, b.updatetime from a, b where a.name = b.name(+)) c where not exists (select 1 from b where b.name = c.name and b.code < c.code);
select * from (select a.id,a.name,b.Code from A a ,B b where a.name =b.name(+) ) d ,(select distinct a.id,a.name from A a ,B b where a.name= b.name) e where d.id = e.id
SQL> with ta as( 2 select 1 id,'test1' name from dual union all 3 select 2,'test2' from dual union all 4 select 3,'test3' from dual) 5 ,tb as( 6 select 11 id,'test1' name,'001' code from dual union all 7 select 12,'test1','002' from dual union all 8 select 13,'test2','004' from dual) 9 select id,nvl(code,'null') code 10 from ( 11 select row_number() over (partition by ta.id order by ta.id) r_id, 12 ta.id id,tb.code code 13 from ta,tb 14 where ta.name=tb.name(+)) 15 where r_id=1 16 /
from A a left join B b
on a.name = b.name;select a.id,nvl(b.code,'null'),nvl(b.updatetime,'null')
from A a , B b
where a.name = b.name(+);
SELECT *
FROM (SELECT a.id, a.name, b.code FROM a LEFT JOIN b ON a.name = b.name) c
WHERE NOT EXISTS (SELECT 1
FROM b
WHERE b.name = c.name
AND b.code < c.code);
--先去掉重复,再连接
SELECT a.id, a.name, c.code
FROM a
LEFT JOIN (SELECT *
FROM b
WHERE NOT EXISTS (SELECT *
FROM b b1
WHERE b.name = b1.name
AND b.code > b1.code)) c
ON a.name = c.name;
from (select a.id, a.name, b.code, b.updatetime
from a, b
where a.name = b.name(+)) c
where not exists (select 1
from b
where b.name = c.name
and b.code < c.code);
SQL> with ta as(
2 select 1 id,'test1' name from dual union all
3 select 2,'test2' from dual union all
4 select 3,'test3' from dual)
5 ,tb as(
6 select 11 id,'test1' name,'001' code from dual union all
7 select 12,'test1','002' from dual union all
8 select 13,'test2','004' from dual)
9 select id,nvl(code,'null') code
10 from (
11 select row_number() over (partition by ta.id order by ta.id) r_id,
12 ta.id id,tb.code code
13 from ta,tb
14 where ta.name=tb.name(+))
15 where r_id=1
16 /
ID CODE
---------- ----
1 001
2 004
3 null