Oracle表自连接问题：如何正确返回主副联系人关联结果

原表结构与数据 #

现有table_1表结构及数据如下：

其中Type=0代表主联系人（Primary contact），Type=2代表副联系人（Secondary contact），相同Common_ID的联系人彼此关联。

需求 #

需要生成如下宽表：

用户原SQL及问题分析 #

用户尝试编写自连接SQL实现需求，但无法返回主联系人99及其关联的副联系人13：

select T1.person_ID,T2.person_ID, T3.person_ID, T4.person_ID  
from table_1 T1
left outer join table_1 T2 on T1.Common_ID=T2.Common_ID
left outer join table_1 T3 on T1.Common_ID=T3.Common_ID
left outer join table_1 T4 on T1.Common_ID=T4.Common_ID
where T1.type=0
and T2.type=2
and T3.type=2
and T4.type=2
and T1.person_ID <> T2.person_ID
and T1.person_ID <> T3.person_ID
and T1.person_ID <> T4.person_ID
and T2.person_ID <> T3.person_ID
and T2.person_ID <> T4.person_ID
and T3.person_ID <> T4.person_ID

问题根源：

1. 将LEFT JOIN的过滤条件（如T2.type=2）放在WHERE子句中，会把LEFT JOIN转为INNER JOIN——当某个Common_ID没有足够的副联系人时，T3、T4的字段会为NULL，而WHERE子句中T3.type=2这类条件会直接过滤掉这些记录。
2. Oracle中NULL参与比较运算（如<>）会返回UNKNOWN，导致包含NULL的记录被过滤，这就是主联系人99的记录无法返回的直接原因。

修正方案 #

方案一：调整自连接的过滤逻辑 #

将副联系人的过滤条件移到JOIN的ON子句中，同时处理NULL值的比较：

select 
    T1.person_ID as Primary,
    T2.person_ID as Other1,
    T3.person_ID as Other2,
    T4.person_ID as Other3
from table_1 T1
left outer join table_1 T2 
    on T1.Common_ID=T2.Common_ID 
    and T2.type=2 
    and T1.person_ID <> T2.person_ID
left outer join table_1 T3 
    on T1.Common_ID=T3.Common_ID 
    and T3.type=2 
    and T1.person_ID <> T3.person_ID
    and (T2.person_ID is null or T2.person_ID <> T3.person_ID)
left outer join table_1 T4 
    on T1.Common_ID=T4.Common_ID 
    and T4.type=2 
    and T1.person_ID <> T4.person_ID
    and (T2.person_ID is null or T2.person_ID <> T4.person_ID)
    and (T3.person_ID is null or T3.person_ID <> T4.person_ID)
where T1.type=0

方案二：使用窗口函数+PIVOT（更优雅的行转列方案） #

这种方法更灵活，即使副联系人数量变化也无需修改连接逻辑：

with contact_ranked as (
    select 
        Common_ID,
        Person_ID,
        case Type 
            when 0 then 'Primary'
            else 'Other' || row_number() over(partition by Common_ID, Type order by Person_ID)
        end as contact_type
    from table_1
    where Type in (0,2)
)
select 
    Primary,
    Other1,
    Other2,
    Other3
from contact_ranked
pivot (
    max(Person_ID)
    for contact_type in ('Primary' as Primary, 'Other1' as Other1, 'Other2' as Other2, 'Other3' as Other3)
)

解释：

• 先用contact_ranked子查询给每个副联系人按Common_ID分组编号，生成Other1、Other2这类标识。
• 再用PIVOT将行数据转置为列，自动处理没有对应副联系人时的NULL值。

内容的提问来源于stack exchange，提问作者Little_Tomato