左侧外部联接丢失的记录
左侧外部联接丢失的记录
提问于 2016-09-22 03:31:50
我正在尝试列出我的PartsOnHand中丢失的记录。如果我的inventory表列出了一个项目，并且它与我的PartsOnHand无关，我想列出每个记录缺少的项目。
下面是我尝试使用的查询：
select *
from inventory i
left outer join PartsOnHand ph on i.InName = ph.InName 我的表结构如下：
库存：
InName
-----------
Wrench
Hammer
PenPartsOnHand：
id Inname
------------------
505 Hammer
505 Wrench
501 Wrench
501 Hammer 预期结果：
505 Pen
501 Pen示例数据：
Select *
FROM (
with inventory as (
select 'Wrench' as InName from dual
union
select 'Hammer' from dual
union
select 'Pen' from dual
), partsonhand as (
select '505' as id, 'Wrench' InName from dual
union
select '505' as id, 'Hammer' InName from dual
union
select '501' as id, 'Wrench' InName from dual
union
select '501' as id, 'Hammer' InName from dual
)
select *
from inventory i
left outer join PartsOnHand ph on i.InName = ph.InName
)回答 3
Stack Overflow用户
发布于 2016-09-22 03:40:08
您可以有一个带有列ID (主键，并且PartsOnHand中的列id指向它)的附加表ALL_ID，这将是正确的设置；或者您希望从表PartsOnHand中读取ID。我将假定是前者；如果是后者，则可以通过从PartsOnHand中选择distinct id将ALL_ID创建为子查询
select id, inname
from all_id cross join inventory
where (id, inname) not in (select id, inname from partsonhand)
;这假设PartsOnHand中没有NULLS (应该不会有NULLS否则就会有数据问题)。
Stack Overflow用户
发布于 2016-09-22 03:45:00
为此，您可以通过CROSS JOIN创建Id和InName的所有不同可能组合，并对结果执行RIGHT JOIN：
Select L.*
From PartsOnHand P
Right Join
(
Select Id, InName
From Inventory I
Cross Join
(
Select Distinct Id
From PartsOnHand P
) X
) L On L.id = P.id
And L.InName = P.InName
Where P.id Is Null结果：
Id InName
501 Pen
505 PenStack Overflow用户
发布于 2016-09-22 03:59:48
将相同的ID分配给不同的名称似乎很奇怪...(我怎么知道Pen的ID是505和501?)但这在示例中提供了所需的结果：
with inventory as (
select 'Wrench' as InName from dual
union
select 'Hammer' from dual
union
select 'Pen' from dual
), partsonhand as (
select '505' as id, 'Wrench' InName from dual
union
select '501' as id, 'Hammer' InName from dual
union
select '505' as id, 'Wrench' InName from dual
union
select '501' as id, 'Hammer' InName from dual
)
select C.ID, I.InName
from inventory i
LEFT join partsonhand P
on I.Inname = P.InName
cross join (Select distinct ID from partsonHand) C
Where P.ID is null
order by ID Desc;
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/39625089
复制相关文章
相似问题
腾讯云开发者
