sql - 困难连接 (SQL)

Question

我有 2 个 sql 数据集:

数据集 1:

select * from (
       Select 1 rn, 'b' c, 3 Val from dual  UNION ALL
       Select 1 rn, 'r' c, 3 Val from dual  UNION ALL
       Select 1 rn, 'w' c, 3 Val from dual  UNION ALL
       Select 2 rn, 'b' c, 2 Val from dual  UNION ALL
       Select 2 rn, 'r' c, 2 Val from dual  UNION ALL
       Select 3 rn, 'b' c, 1 Val from dual  

) t1

数据集 2:

select * from ( 
       Select 'b' c from dual  UNION ALL
       Select 'r' c from dual  UNION ALL
       Select 'w' c from dual ) t2
   

我需要获取以下数据集:

rn  c   val
1   b   3 
1   r   3 
1   w   3 
2   b   2 
2   r   2 
2   w   null 
3   b   1 
3   r   null 
3   w   null

我尝试使用以下 sql 执行此操作，但它不起作用。

select t1.*, t2.*
FROM (
       Select 1 rn, 'b' c, 3 Val from dual  UNION ALL
       Select 1 rn, 'r' c, 3 Val from dual  UNION ALL
       Select 1 rn, 'w' c, 3 Val from dual  UNION ALL
       Select 2 rn, 'b' c, 2 Val from dual  UNION ALL
       Select 2 rn, 'r' c, 2 Val from dual  UNION ALL
       Select 3 rn, 'b' c, 1 Val from dual  

) t1 right join ( 
       Select 'b' c from dual  UNION ALL
       Select 'r' c from dual  UNION ALL
       Select 'w' c from dual ) t2 on   t1.c=t2.c 
       order by 1,2
   

请帮我解决我的问题。我使用 Oracle 数据库。

Answer 1

这是分区外连接的确切用例。对于大型数据集，它应该比 CROSS JOIN 方法更有效。

语法是这样的:

select t1.rn, 
       t2.c,
       t1.val
from   t1 partition by (rn) right join t2 on t2.c = t1.c;

PARTITION BY 关键字告诉 Oracle 为 t1.rn 的每个不同值重复外部连接。

这是一个完整的例子:

with t1 as (
     Select 1 rn, 'b' c, 3 Val from dual  UNION ALL
     Select 1 rn, 'r' c, 3 Val from dual  UNION ALL
     Select 1 rn, 'w' c, 3 Val from dual  UNION ALL
     Select 2 rn, 'b' c, 2 Val from dual  UNION ALL
     Select 2 rn, 'r' c, 2 Val from dual  UNION ALL
     Select 3 rn, 'b' c, 1 Val from dual
  ),
  t2 as (
     Select 'b' c from dual  UNION ALL
     Select 'r' c from dual  UNION ALL
     Select 'w' c from dual
  )
 select t1.rn, 
        t2.c,
        t1.val
 from   t1 partition by (rn) right join t2 on t2.c = t1.c;

+----+---+-----+
| RN | C | VAL |
+----+---+-----+
|  1 | b |   3 |
|  1 | r |   3 |
|  1 | w |   3 |
|  2 | b |   2 |
|  2 | r |   2 |
|  2 | w |   - |
|  3 | b |   1 |
|  3 | r |   - |
|  3 | w |   - |
+----+---+-----+