SQL 查询 - 两个表的间接连接

Question

我有如下两个表

表1

COL1    COL2    COL3
 A       10     ABC
 A       11     ABC
 A       1      DEF
 A       2      DEF
 B       10     ABC
 B       11     ABC
 B       1      DEF
 C       3      DEF
 C       12     ABC
 C       21     GHI

表2

COL1   GHI  ABC DEF
 A1    21   10  1
 A2    21   12  1
 A3    21   10  1
 A4    23   10  1
 A5    25   11  3
 A6    21   14  3
 A7    25   11  1
 A8    23   10  1
 A9    29   10  2
 A10   21   12  3

我创建了另一个临时表，它返回来自 tbl1.col1 的所有不同值

tbl1 中 col3 的值是 tbl2 中的列，由一些值填充。

我需要的是 table1.column1 的每个不同值，(A, B, C) 在这种情况下，返回 table2.column1 的组合和 table1.column1 这样

• table2.column1 的 ABC 值与 table1 中“组”的任何 ABC 值匹配，
• AND table2.column1 的 DEF 值与 table1 中“组”的任何 DEF 值匹配，
• AND IF THE GROUP CONTAINS GHI VALUES，table2.column1 的 GHI 值与 table1 中“group”的任何 GHI 值匹配

所以，我需要像下面这样的东西

输出表

Table2.COL1   Table1.Col1
     A1            A
     A3            A
     A4            A
     A7            A
     A8            A
     A9            A
     A1            B
     A3            B
     A4            B
     A7            B
     A8            B          
     A10           C

我尝试过类似的方法，但我不确定这是否是正确的方法

select table2.col1, temp_distinct_table.column1 
      from table2, temp_distinct_table
      where table2.def IN (SELECT col2 
                           FROM table1 
                           WHERE table1.col1 = temp_distinct_table.col1 
                                 AND table1.col3 = 'DEF')
      AND table2.abc IN (SELECT col2 
                         FROM table1 
                         WHERE table1.col1 = temp_distinct_table.col1 
                                 AND table1.col3 = 'ABC')
      AND (
          table2.ghi IN (SELECT col2 
                          FROM table1 
                          WHERE table1.col1 = temp_distinct_table.col1  
                                 AND table1.col3 = 'GHI') 
         OR NOT EXISTS (SELECT col2 
                     FROM table1 
                     WHERE table1.col1 = temp_distinct_table.col1 
                       AND table1.col3 = 'GHI')
          )

其中 temp_distinct_table 包含来自 table1.col1 的所有不同值

有人可以指导我解决这个问题吗？

Answer 1

另一种方法，在加入所有可能的匹配后，计算每个 t1.col/t2.col 组合有多少匹配:

select distinct t2_col1, t1_col1
from (
  select t2.col1 as t2_col1, t1.col1 as t1_col1, t1.ghi_count as t1_ghi_count,
    count(case when t1.col3 = 'ABC' then 1 end)
      over (partition by t1.col1, t2.col1) as abc_matches,
    count(case when t1.col3 = 'DEF' then 1 end)
      over (partition by t1.col1, t2.col1) as def_matches,
    count(case when t1.col3 = 'GHI' then 1 end)
      over (partition by t1.col1, t2.col1) as ghi_matches
  from (
    select t1.*,
      count(case when t1.col3 = 'GHI' then 1 end)
        over (partition by t1.col1) as ghi_count
    from table1 t1
  ) t1
  join table2 t2
  on (t1.col3 = 'ABC' and t2.abc = t1.col2)
  or (t1.col3 = 'DEF' and t2.def = t1.col2)
  or (t1.col3 = 'GHI' and t2.ghi = t1.col2)
)
where abc_matches > 0
and def_matches > 0
and (t1_ghi_count = 0 or ghi_matches > 0)
order by t1_col1, t2_col1;

您的样本数据得到:

T2_COL T1_COL
------ ------
A1     A     
A3     A     
A4     A     
A7     A     
A8     A     
A9     A     
A1     B     
A3     B     
A4     B     
A7     B     
A8     B     
A10    C     

不确定这样做的效率是否与 MTO 与您的真实数据的交叉联接有很大不同。