请教ORACLE Left Join的问题

Question

大家好，我现在遇到一个问题，希望有经验的同学帮忙解答一下，问题如下：有数据表A如下：
a1   a2
1    1
2    1
2    2
1    3
2    3
说明：a1列的值为枚举值（1,2），a2列值不确定，为正整数；数据表B如下：
b1   b2
1    30
2    40
3    50
说明：b1列的类型和a2列完全一样，b2列不关心。现在我想要的结果如下：
A.a1   A.a1   B.b2
1       1      30
2       1      0
2       2      40
1       3      50
2       3      0
说明：大概的思路是A表要left join B表，条件是A.a2 = B.b1,关键的需求如下：
如果针对一个A.a2值，A表中存在A.a1 = 1的记录，则把B.b2关联到这条记录上，且A.a1=2对于的记录行B.b2=0;
如果针对一个A.a2值，A表中不存在A.a1 = 1的记录，则把B.b2关联到A.a1=2记录上;初步的SQL如下：
select A.a1, A.a2, B.b2 from A
Left Join B
ON A.a2 = B.b1主要是B.b2如何实现，多谢！

Answer 1

select A.a1, A.a2, decode(A.a1,1,nvl(B.b2,0),0)b2 from A
Left Join B
ON A.a2 = B.b1

Answer 2

这样的结果是：
A.a1 A.a1 B.b2
1 1 30
2 1 0
2 2 0
1 3 50
2 3 0第三行与需求不符，需求是如果A.a2 = 2 且不存在A.a1 = 1的情况,将B.b2关联到A.a1=2的记录上。

Answer 3

select t.a1,t.a2,decode(t.rn,1,t.b2,0) b2
from (select A.a1, A.a2,B.b2,row_number() over (partition by A.a2 order by A.a1) rn
     from A
     Left Join B
     ON A.a2 = B.b1) t;

Answer 4

SELECT A.A1, A.A2,
       CASE WHEN (CNT1 = 2 OR (CNT1 = 1 AND CNT2 = 1)) AND A.A1 = 1 THEN B.B2 
            WHEN CNT1 = 1 AND CNT2 = 0 THEN B.B2 ELSE 0 END B2         
  FROM A, B, (SELECT A.A2,
                     COUNT(DISTINCT A.A1) CNT1,
                     SUM(DECODE(A.A2, 2, 0, 1)) CNT2
                FROM A
                GROUP BY A.A2) C
 WHERE A.A2 = B.B1
   AND A.A2 = C.A2
ORDER BY 2,1;--测试。。
[SYS@myoracle] SQL>WITH A AS(
  2    SELECT 1 A1,1 A2 FROM DUAL UNION ALL
  3    SELECT 2 A1,1 A2 FROM DUAL UNION ALL
  4    SELECT 2 A1,2 A2 FROM DUAL UNION ALL
  5    SELECT 1 A1,3 A2 FROM DUAL UNION ALL
  6    SELECT 2 A1,3 A2 FROM DUAL),
  7  B AS(
  8    SELECT 1 B1,30 B2 FROM DUAL UNION ALL
  9    SELECT 2 B1,40 B2 FROM DUAL UNION ALL
 10    SELECT 3 B1,50 B2 FROM DUAL)
 11  SELECT A.A1, A.A2,
 12         CASE WHEN (CNT1 = 2 OR (CNT1 = 1 AND CNT2 = 1)) AND A.A1 = 1 THEN B.B2
 13              WHEN CNT1 = 1 AND CNT2 = 0 THEN B.B2 ELSE 0 END B2
 14    FROM A, B, (SELECT A.A2,
 15                       COUNT(DISTINCT A.A1) CNT1,
 16                       SUM(DECODE(A.A2, 2, 0, 1)) CNT2
 17                  FROM A
 18                  GROUP BY A.A2) C
 19   WHERE A.A2 = B.B1
 20     AND A.A2 = C.A2
 21  ORDER BY 2,1
 22  ;        A1         A2         B2
---------- ---------- ----------
         1          1         30
         2          1          0
         2          2         40
         1          3         50
         2          3          0[SYS@myoracle] SQL>

Answer 5

with tbl_a as
(
    select 1 as a1, 1 as a2 from dual
     union all
    select 2 as a1, 1 as a2 from dual
     union all
    select 2 as a1, 2 as a2 from dual
     union all
    select 1 as a1, 3 as a2 from dual
     union all
    select 2 as a1, 3 as a2 from dual
),
tbl_b as
(
    select 1 as b1, 30 as b2 from dual
     union all
    select 2 as b1, 40 as b2 from dual
     union all
    select 3 as b1, 50 as b2 from dual
)
select a.*, case when a.a1 = 1 then b.b2
                 when a.a1 = 1 and a.a2 = 2 then 0
                 when a.a2 = 2 then b.b2
                 else 0
             end b2
  from tbl_a a left join tbl_b b
    on a.a2 = b.b1;

Answer 6

纠正一下：SUM(DECODE(A.A2, 2, 0, 1))
-- 笔误
SUM(DECODE(A.A1, 2, 0, 1))SELECT A.A1, A.A2,
       CASE WHEN (CNT1 = 2 OR (CNT1 = 1 AND CNT2 = 1)) AND A.A1 = 1 THEN B.B2 
            WHEN CNT1 = 1 AND CNT2 = 0 THEN B.B2 ELSE 0 END B2         
  FROM A, B, (SELECT A.A2,
                     COUNT(DISTINCT A.A1) CNT1,
                     SUM(DECODE(A.A2, 2, 0, 1)) CNT2
                FROM A
                GROUP BY A.A2) C
 WHERE A.A2 = B.B1
   AND A.A2 = C.A2
ORDER BY 2,1;

Answer 7

WITH A AS(
  SELECT 1 A1,1 A2 FROM DUAL UNION ALL
  SELECT 2 A1,1 A2 FROM DUAL UNION ALL
  SELECT 2 A1,4 A2 FROM DUAL UNION ALL
  SELECT 1 A1,3 A2 FROM DUAL UNION ALL
  SELECT 2 A1,3 A2 FROM DUAL),
B AS(
  SELECT 1 B1,30 B2 FROM DUAL UNION ALL
  SELECT 2 B1,40 B2 FROM DUAL UNION ALL
  SELECT 3 B1,50 B2 FROM DUAL UNION ALL
  SELECT 4 B1,60 B2 FROM DUAL)对这样的数据有点问题。