Oracle SQL中高效的四向连接

概述我有两个表,Table1和Table2,我想加入.每个表都有两个唯一的键,我们称之为Key1和Key2.我想做的是LEFT JOIN Table2到Table1,其中任何一个键匹配四种可能的组合中的任何一种： > Table1.Key1 = Table2.Key1 > Table1.Key1 = Table2.Key2 > Table1.Key2 = Table2.Key1 > Table1.Ke 我有两个表,table1和table2,我想加入.每个表都有两个唯一的键,我们称之为Key1和Key2.我想做的是left JOIN table2到table1,其中任何一个键匹配四种可能的组合中的任何一种：

> table1.Key1 = table2.Key1
> table1.Key1 = table2.Key2
> table1.Key2 = table2.Key1
> table1.Key2 = table2.Key2

我的问题是：有没有有效的方法来做到这一点？现在我想出了类似的东西,但它需要永远运行.

CREATE table NEW_table ASSELECT a.*,CASE WHEN a.Key1 = b.Key1 THEN 1 ELSE 0 END AS match1,CASE WHEN a.Key1 = c.Key2 THEN 1 ELSE 0 END AS match2,CASE WHEN a.Key2 = b.Key1 THEN 1 ELSE 0 END AS match3,CASE WHEN a.Key2 = c.Key2 THEN 1 ELSE 0 END AS match4FROM table1 aleft JOIN (Select Key1 From table2 Where Key1 is not null) b    on a.Key1 = b.Key1 or a.Key2 = b.Key1left JOIN (Select Key2 From table2 Where Key2 is not null) c    on a.Key1 = c.Key2 or a.Key2 = c.Key2;

绝望,我知道……

编辑：以下示例数据和所需结果：

表格1：

Key1     Key2     Sales    Revenueqwer!@   dhfgfw   455      30005asdf#$  dfg654   221      28711edfr2#   gg%%^f   213      31667gthy!2   awd^&5   133      13345rf$#22   34ffgg   655      41237bhjk%g   w3erff   122      10066f&*yhj   dffghj   126      11004

表2：

Key1     Key2 qwer!@   {null}{null}   dfg654ffgww2   ppolkk{null}   gthy!2jjjj33   l00kjlnmnmnm   34ffggawd^&5   {null}

期望的结果：

Key1     Key2     Sales    Revenue   match1    match2    match3    match4qwer!@   dhfgfw   455      30005     1         0         0         0asdf#$  dfg654   221      28711     0         0         0         1edfr2#   gg%%^f   213      31667     0         0         0         0gthy!2   awd^&5   133      13345     1         0         1         0rf$#22   34ffgg   655      41237     0         0         0         1bhjk%g   w3erff   122      10066     0         0         0         0f&*yhj   dffghj   126      11004     0         0         0         0

解决方法 如果你只需要行的组合而不是排列(即如果table1.key与table2.key1和table2.key2具有相同的值,那么只返回一行),那么这应该有效：

SELECT a.*,CASE WHEN a.Key1 = b.Key2 THEN 1 ELSE 0 END AS match2,CASE WHEN a.Key2 = b.Key2 THEN 1 ELSE 0 END AS match4FROM table1 aleft JOIN table2 b    on a.Key1 in (b.Key1,b.key2) or a.key2 in (b.key1,b.key2);

插入提供的数据,此解决方案确实有效,但需要进行汇总以提供您正在寻找的结果：

SELECT   a.key1,a.key2,a.sales,a.revenue,MAX (CASE WHEN a.key1 = b.key1 THEN 1 ELSE 0 END) AS match1,MAX (CASE WHEN a.key1 = b.key2 THEN 1 ELSE 0 END) AS match2,MAX (CASE WHEN a.key2 = b.key1 THEN 1 ELSE 0 END) AS match3,MAX (CASE WHEN a.key2 = b.key2 THEN 1 ELSE 0 END) AS match4FROM     table1 a         left JOIN table2 b            ON a.key1 IN (b.key1,b.key2) OR a.key2 IN (b.key1,b.key2)GROUP BY a.key1,a.revenue;

总结

以上是内存溢出为你收集整理的Oracle SQL中高效的四向连接全部内容，希望文章能够帮你解决Oracle SQL中高效的四向连接所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。