如何与选择性重复记录连接表?(oracle10g)

Question

如何为以下需求创建ORACLE查询?

问题是，如果相同的ID存在于一些SQL结果中，我如何获得该ID的记录，其中名称不是空白或NULL。<>之前模式(1)Sql1 => result1 => id name----- ------001002003

SQL2 => RESULT2 => ID NAME----- ------003年NAME1

SQL3 => RESULT3 => ID NAME----- ------003年NAME2之前<>之前模式(2)Sql1 => result1 => id name----- ------001002003年NAME1

SQL2 => RESULT2 => ID NAME----- ------003

SQL3 => RESULT3 => ID NAME----- ------003年NAME2

<>之前模式(3)Sql1 => result1 => id name----- ------001002003

SQL2 => RESULT2 => ID NAME----- ------003年NAME1003年NAME2

SQL3 => RESULT3 => ID NAME----- ------

之前我如何将这3个结果合并成下面的结果?

<>之前身份证的名字----- ------001002003年NAME1003年NAME2

Answer 1

一种选择是将SQL2和SQL3表合并到UNION，然后将此结果连接回SQL1:

SELECT t1.ID, t1.NAME
FROM SQL1 t1 LEFT JOIN
(
    SELECT ID, NAME
    FROM SQL2
    UNION ALL
    SELECT ID, NAME
    FROM SQL3
) t2
    ON t1.ID = t2.ID

Answer 2

这适用于您的场景

with tbl(id,name) as
(query1 
 union all
query2
 union all
query 3
)
select * From tbl where name is null
 and id not in
    (select id from tbl where name is not null)
union all
select * from tbl where name is not null

我已经创建了示例

with tbl(id,name) as
(select 1,null from dual union all
select 2,null from dual union all
select 3,null from dual union all
select 3,'NAME1' from dual union all
select 3,'NAME2' from dual)
select * From tbl where name is null
 and id not in
    (select id from tbl where name is not null)
union all
select * from tbl where name is not null

输出

id  name
---------
1   
2   
3   NAME1
3   NAME2