如果表2包含更多，则Oracle SQL联合表1与表2的结果为null

Question

我将尝试用一个例子来解释。

我有两张桌子：

table1
column1 column2 column3
000001   ABC     COD1
000001   ABC     COD2
000002   BCD     COD3
000003   EDF     COD1
000003   EDF     COD3
000004   FGH     COD1
000004   FGH     COD2
000004   FGH     COD3
000004   FGH     COD4
table2
column3
COD1
COD2
COD3
COD4
COD5
COD6
COD7

表2只包含表1的第3列的所有可能的代码(并且仅包含该代码(。表1包含我的真实数据。我不想要表2 COD5 COD6和COD7

我必须得到这样一个的结果

000001   ABC     COD1
000001   ABC     COD2
null     null    COD3
null     null    COD4
null     null    COD1
null     null    COD2
000002   BCD     COD3
null     null    COD4
000003   EDF     COD1
null     null    COD2
000003   EDF     COD3
null     null    COD4
000004   FGH     COD1
000004   FGH     COD2
000004   FGH     COD3
000004   FGH     COD4

我需要用oraclesql来做这件事，但我不确定这是否可能。如果需要，我可以接受第一列始终是我的当前列1(即使列2为空(。我不想要重复，例如两个000004 FGH COD4，但如果为null，我想要重复。

我尝试了我所知道的一切。。。但我能做的最好的事情是在需要指定column1条件的子查询上创建一个带有负联接或右联接的并集。

编辑这是一个朋友在我尝试时找到的正确答案

select * from
(select distinct a.col1, a.col2
from table1 a
right join (select col3 from table2 f
where f.col3 IN ('COD1','COD3','COD6')) b
on a.col3 = b.col3),
(select col3 from table2 f
where f.col3 IN ('COD1','COD3','COD6'));

Answer 1

以下查询将完成

with t2 as (
select c3
from t2
where c3 in ('COD1', 'COD2', 'COD3', 'COD4')
)
,t3 as (
select distinct
c1
,c2
from t1
)    
,t_all as (
select t3.c1
,t3.c2
,t2.c3
from t3
,t2 
)
select t1.c1
,t1.c2
,t_all.c3
from t_all left join t1 on 
t1.c1 = t_all.c1 and t1.c2 = t_all.c2 and t1.c3 = t_all.c3

Answer 2

您可以使用cross join生成行，然后使用left join:

select t1.col1, t1.col2, c3.col3
from (select distinct col1 from table1) c1 cross join
table2 c3 left join
table1 t1
on t1.col1 = c1.col1 and t1.col3 = c3.col3
order by c1.col1, c3.col3;

注意：前两列中有NULL值，这似乎很奇怪。这会导致行完全重复。如果使用select c1.col1, t1.col2, c3.col3，则第一列中会有有效值。

Answer 3

您需要按如下方式使用cross join和left join：

SQL> with table1 (col1,col2,col3) as
2  (select '000001','ABC','COD1' from dual union all
3  select '000001','ABC','COD2' from dual union all
4  select '000002','BCD','COD3' from dual union all
5  select '000003','EDF','COD1' from dual union all
6  select '000003','EDF','COD3' from dual ),
7  TABLE2 (COL3) AS
8  (SELECT 'COD1' FROM DUAL UNION ALL
9  SELECT 'COD2' FROM DUAL UNION ALL
10  SELECT 'COD3' FROM DUAL UNION ALL
11  SELECT 'COD4' FROM DUAL UNION ALL
12  SELECT 'COD5' FROM DUAL)
13  select t11.col1, T11.COL2, T2.COL3
14  from (SELECT DISTINCT COL1 FROM TABLE1) T1
15  CROSS JOIN (SELECT * FROM TABLE2 WHERE COL3 IN (SELECT COL3 FROM TABLE1)) T2
16  LEFT JOIN TABLE1 T11 ON T11.COL1 = T1.COL1 AND T2.COL3 = T11.COL3
17  ORDER BY T1.COL1, T2.COL3;
COL1   COL COL3
------ --- ----
000001 ABC COD1
000001 ABC COD2
COD3
COD1
COD2
000002 BCD COD3
000003 EDF COD1
COD2
000003 EDF COD3
9 rows selected.
SQL>

Answer 4

您正在寻找分区外部联接a"新的"；功能从2004年开始出现，但仍未广泛使用。

期望的是，对于column3的所有值，table1至少包含一个值，但column2是稀疏的，必须填充。(注意，您甚至根本不需要table2(

只有当您希望column2为NULL时，查询才是直接的。在添加它的情况下，您必须使用case语句重置它：

select 
tab.column1, 
case when tab.column1 is NOT NULL then tab.column2 end column2, 
c3.column3
from tab
partition by (column2)
right outer join 
(select distinct column3 from tab) c3
on tab.column3 = c3.column3
order by tab.column2, c3.column3;

这将根据要求生成数据：

COLUMN COL COLU
------ --- ----
000001 ABC COD1
000001 ABC COD2
COD3
COD4
COD1
COD2
000002 BCD COD3
COD4
000003 EDF COD1
COD2
000003 EDF COD3
COD4
000004 FGH COD1
000004 FGH COD2
000004 FGH COD3
000004 FGH COD4

请注意，手动解决方案(即在分区的外部联接之前(是从table1获取列2和3的所有组合-请参阅下面的子查询comb。

在第二步中，简单的outer joincomb到您的表

with col2 as(
select distinct column2 from tab),
col3 as (
select distinct column3 from tab),
comb as (
select *
from col2 cross join col3)
select tab.column1, tab.column2, comb.column3 from comb
left outer join tab 
on comb.column2 = tab.column2 and comb.column3 = tab.column3
order by comb.column2, comb.column3
;

这给出了相同的结果。