Oracle连接视图的最佳匹配

Question

我想创建一个连接两个表的视图。

T1 =

Col1 Col2
AA   BB
EE   FF
YY   ZZ
11   00

T2 =

Col1 Col2 Col3
AA   BB   1
AA   CC   2
CC   BB   3
GG   FF   4
GG   HH   5
EE   HH   6
XX   YY   7
XX   WW   8
YY   RR   9

此视图的规则是基于以下规则的最佳匹配场景:

1. Return Col3 from T2 if T1.Col1 & T1.Col2 = T2.Col1 & T2.Col2
ELSE
2. Return Col3 if T1.Col2 = T2.Col2
ELSE
3. Return Col3 if T1.Col = T2.Col1
ELSE
4. Return NULL

所以在这些例子中，我希望最终视图包含:

AA BB 1   (Rule 1 match)
EE FF 4   (Rule 2 match)
YY ZZ 9   (Rule 3 match)
11 00 NULL (Rule 4 match)

我遇到的困难是在它击中多个规则的情况下(例如第1行和第3行，规则1和2被击中，或第4行和第6行，规则2和3被分别击中)。

我意识到在这个例子中，规则3被多次击中——这很好，因为它的想法是，只有当其他规则不为真时，它才会击中规则3，应该只产生一个结果(如在例子3中)。

是否有一种方法可以做类似的联合来满足这些级联规则，或者是否需要使用预过滤创建额外的视图(例如有count <2)吗?

在excel中的公式是:=如果(和(A3 = F 2美元,B3 = $ G 2美元),"规则1",如果((B3 = G 2美元),"规则2",如果((A3 = F 2美元),"规则3","零")))

其中A3 = T2。Col1 B3 = T2。Col2 G2 = T1。

Answer 1

我想这样做:

with t1 as (select 'AA' col1, 'BB' col2 from dual union all
            select 'EE' col1, 'FF' col2 from dual union all
            select 'YY' col1, 'ZZ' col2 from dual union all
            select '11' col1, '00' col2 from dual),
     t2 as (select 'AA' col1, 'BB' col2, 1 col3 from dual union all
            select 'AA' col1, 'CC' col2, 2 col3 from dual union all
            select 'CC' col1, 'BB' col2, 3 col3 from dual union all
            select 'GG' col1, 'FF' col2, 4 col3 from dual union all
            select 'GG' col1, 'HH' col2, 5 col3 from dual union all
            select 'EE' col1, 'HH' col2, 6 col3 from dual union all
            select 'XX' col1, 'YY' col2, 7 col3 from dual union all
            select 'XX' col1, 'WW' col2, 8 col3 from dual union all
            select 'YY' col1, 'RR' col2, 9 col3 from dual),
    res as (select t1.col1,
                   t1.col2,
                   t2.col3,
                   case when t1.col1 = t2.col1 and t1.col2 = t2.col2 then 1
                        when t1.col2 = t2.col2 then 2
                        when t1.col1 = t2.col1 then 3
                   end join_level,
                   min (case when t1.col1 = t2.col1 and t1.col2 = t2.col2 then 1
                             when t1.col2 = t2.col2 then 2
                             when t1.col1 = t2.col1 then 3
                        end) over (partition by t1.col1, t1.col2) min_join_level
            from   t1
                   left outer join t2 on (t1.col1 = t2.col1 or t1.col2 = t2.col2))
select col1,
       col2,
       col3
from   res
where  join_level = min_join_level
or     join_level is null;

COL1 COL2       COL3
---- ---- ----------
11   00                                       
AA   BB            1
EE   FF            4
YY   ZZ            9

Ie。首先执行连接(在本例中，t1 left outer join t2 on (t2.col1 = t1.col1 or t2.col2 = t1.col2)包括t1.col1 = t2.col1 and t1.col2 = t2.col2所在的行)，然后根据连接条件优先过滤结果。

---

这里有一个稍微不同的选择，使用聚合而不是像上面的答案那样的分析函数:

with t1 as (select 'AA' col1, 'BB' col2 from dual union all
            select 'EE' col1, 'FF' col2 from dual union all
            select 'YY' col1, 'ZZ' col2 from dual union all
            select '11' col1, '00' col2 from dual),
     t2 as (select 'AA' col1, 'BB' col2, 1 col3 from dual union all
            select 'AA' col1, 'CC' col2, 2 col3 from dual union all
            select 'CC' col1, 'BB' col2, 3 col3 from dual union all
            select 'GG' col1, 'FF' col2, 4 col3 from dual union all
            select 'GG' col1, 'HH' col2, 5 col3 from dual union all
            select 'EE' col1, 'HH' col2, 6 col3 from dual union all
            select 'XX' col1, 'YY' col2, 7 col3 from dual union all
            select 'XX' col1, 'WW' col2, 8 col3 from dual union all
            select 'YY' col1, 'RR' col2, 9 col3 from dual)
select t1.col1,
       t1.col2,
       min(t2.col3) keep (dense_rank first order by case when t1.col1 = t2.col1 and t1.col2 = t2.col2 then 1
                                                         when t1.col2 = t2.col2 then 2
                                                         when t1.col1 = t2.col1 then 3
                                                    end) col3
from   t1
       left outer join t2 on (t1.col1 = t2.col1 or t1.col2 = t2.col2)
group by t1.col1,
         t1.col2;
COL1 COL2       COL3
---- ---- ----------
11   00             
AA   BB            1
EE   FF            4
YY   ZZ            9

注意:如果碰巧有不止一行满足最高优先级可用连接条件，则可能返回不同的结果。第一个查询将返回具有(可能)不同col3的每一行，而第二个查询将只返回具有最低可用col3值的一行。

---

如果T2包含:

COL1 COL2       COL3
---- ---- ----------
AA   BB            1
AA   CC            2
CC   BB            3
GG   FF            4
GG   HH            5
EE   HH            6
XX   YY            7
XX   WW            8
YY   RR            9
YY   SS           10

第一个查询将给你:

COL1 COL2       COL3
---- ---- ----------
11   00             
AA   BB            1
EE   FF            4
YY   ZZ           10
YY   ZZ            9

第二个查询将给你:

COL1 COL2       COL3
---- ---- ----------
11   00             
AA   BB            1
EE   FF            4
YY   ZZ            9

Answer 2

可能是这样的方法，它将三个常见表表达式的结果集链接在一起，每个表表达式实现一个不同的连接，并检查T1中行的行宽是否已经从一个成功的连接中投影出来:

with
  first_join as (
    select t1.col1,
           t1.col2,
           t2.col3,
           t1.rowid
    from   t1 join t2 on t1.col1 = t2.col1 and t1.col2 = t2.col2),
  second_join as (
    select t1.col1,
           t1.col2,
           t2.col3,
           t1.rowid
    from   t1 join t2 on  t1.col2 = t2.col2
    where  t1.rowid not in (select rowid from first_join)),
  third_join as (
      select t1.col1,
             t1.col2,
             t2.col3,
      from   t1 join t2 on  t1.col1 = t2.col1
      where  t1.rowid not in (select rowid from first_join union all
                              select rowid from second_join))
select col1, col2, col3 from first_join  union all
select col1, col2, col3 from second_join union all
select col1, col2, col3 from third_join