oracle左外连接不显示正确的空值

我有这个表：

table myTable(refnum,contid,type)

values are:
1,10,90000
2,20,90000
3,30,90000
4,10000
5,10000
6,20000
7,20000
8,20000

这是我以后的领域的分解：

select a.refnum from myTable a where type = 90000
select b.refnum from myTable b where type = 10000 and contid in (select contid from myTable where type = 90000)
select c.refnum from myTable c where type = 20000 and contid in (select contid from myTable where type = 90000)

我以后查询的结果是：

a.refnum,b.refnum,c.refnum

我以为这会工作：

select a.refnum,c.refnum
from myTable a 
left outer join myTable b on (a.contid = b.contid) 
left outer join myTable c on (a.contid = c.contid) 
where a.id_tp_cd = 90000
and b.id_tp_cd = 10000
and c.id_tp_cd = 20000

所以值应该是：

1,null,6
2,4,7
3,5,8

但它唯一的回报：

2,8

我以为左连接会显示左边的所有值,并为右边创建一个空值.

帮帮我 ：(

and b.id_tp_cd = 10000
and c.id_tp_cd = 20000

您应该可以将它们放在连接的“on”子句中,因此只返回右侧的相关行.

select a.refnum,c.refnum
from myTable a 
left outer join myTable b on (a.contid = b.contid and b.id_tp_cd = 10000) 
left outer join myTable c on (a.contid = c.contid and c.id_tp_cd = 20000) 
where a.id_tp_cd = 90000

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!