17,078
社区成员
发帖
与我相关
我的任务
分享
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 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;
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