Oracle SQL自联接:如何包括丢失的记录

Question

即使没有记录，我也要显示NAME和PLAN。我试着在这个上使用self-join，但没有成功：(

脚本：

SELECT DISTINCT    A.NAME  , B.PLAN
FROM PLAYER A  
LEFT JOIN PLAYER B
ON A.NAME = B.NAME 
WHERE A.NAME IN ( 'BOGGIE', 'STEPH')
AND  B.PLAN IN ('PLAN200' , 'PLAN999');

当前结果

NAME    PLAN
BOOGIE  PLAN200
STEPH   PLAN200

预期结果

NAME    PLAN
BOOGIE  PLAN200
BOOGIE  
STEPH   PLAN200
STEPH   

表

NAME    PLAN    AMOUNT  CONTRACT
BOGGIE  PLAN200   200      24
STEPH   PLAN200   200      24

Answer 1

您可以尝试在ON clause中使用put其他条件

SELECT DISTINCT    A.NAME  , B.PLAN
FROM PLAYER A  
LEFT JOIN PLAYER B
ON A.NAME = B.NAME 
and A.NAME IN ( 'BOGGIE', 'STEPH')
AND  B.PLAN IN ('PLAN200' , 'PLAN999');

Answer 2

问题中的样本数据不完整，无法获得所需的输出；请试试下面的。增加BOGGIE和STEPH两行，计划为空；让我们知道这就足够了。

WITH player
AS (SELECT 'BOGGIE' name,
'PLAN200' plan,
200 amount,
24 contract
FROM DUAL
UNION
SELECT 'STEPH ' name,
'PLAN200' plan,
200 amount,
24 contract
FROM DUAL
UNION
SELECT 'BOGGIE' name,
NULL plan,
NULL amount,
NULL contract
FROM DUAL
UNION
SELECT 'STEPH ' name,
NULL plan,
NULL amount,
NULL contract
FROM DUAL)
SELECT DISTINCT a.name, b.plan
FROM player a
LEFT OUTER JOIN player b
ON     a.name = b.name
AND (   a.name IN ('BOGGIE', 'STEPH')
OR b.plan IN ('PLAN200', 'PLAN999')) 

输出

NAME   PLAN   
------ -------
BOGGIE PLAN200
BOGGIE        
STEPH  PLAN200
STEPH 

Answer 3

首先使用两个计划-->cross_result交叉加入PLAYER。一旦你得到了，你会有如下结果集

cross_result
BOOGIE PLAN200
BOOGIE PLAN999
STEPH  PLAN200
STEPH  PLAN999
player
BOOGIE PLAN200
STEPH  PLAN200

在那之后，将cross_result与PLAYER表连接

with cte
as (SELECT 'PLAN200' as plan FROM DUAL
UNION ALL
SELECT 'PLAN999' as plan FROM DUAL
)
,cross_result  
as (select A.PLAYER,A.PLAN
from cte m
join PLAYER A
ON A.NAME IN ( 'BOGGIE', 'STEPH')
)
select a.PLAYER,b.PLAN /*I have choosen b.PLAN, so for a.PLAN=PLAN99 it would display null*/
from cross_result a
left join PLAYER b
on a.player=b.player
and a.plan=b.plan

Answer 4

查询的问题是：

A.NAME IN ( 'BOGGIE', 'STEPH') AND B.PLAN IN ('PLAN200' , 'PLAN999');

查询将left join放在NAME上，但在应用left join后，将对记录进行进一步筛选。应在left join子句中正确添加where子句条件。

下面的查询应该会给出预期的结果。

SELECT DISTINCT    A.NAME  , B.PLAN
FROM PLAYER A 
LEFT JOIN PLAYER B
ON A.NAME = B.NAME
AND (A.NAME IN ( 'BOGGIE', 'STEPH')
OR B.PLAN IN ('PLAN200' , 'PLAN999'));