问一个左连接的问题。。。

wlalw131415 2011-05-23 09:57:30
A表
id name
1 test1
2 test2
3 test3

--------------------------------------------
B表
id name code
11 test1 '001'
12 test1 '002'
13 test2 '004'

--------------------------------------------
oracle数据库:
select a.id,b.code
from A a left join B b
on a.name = b.name;

查出的数据为:
a.id b.code
1 '001'
1 '002'
2 '004'
3 null

------------------------------------------------

如何用左连接使查询出的数据为:
a.id b.code
1 '001'
2 '004'
3 null

谢谢!!
...全文
132 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
Kobayashi 2011-05-23
  • 打赏
  • 举报
回复
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
BlueskyWide 2011-05-23
  • 打赏
  • 举报
回复
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);
tangren 2011-05-23
  • 打赏
  • 举报
回复
--先连接,再去重
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;
wlalw131415 2011-05-23
  • 打赏
  • 举报
回复
顶一下!
秋雨飘落 2011-05-23
  • 打赏
  • 举报
回复
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(+);
jumpheightway 2011-05-23
  • 打赏
  • 举报
回复
a-b
b-a
304的的哥 2011-05-23
  • 打赏
  • 举报
回复

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

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧