关于两个表的连接的查询

Question

假设我有下面两张表

sql> select * from fraud_types ;

fraud_id       fraud_name
--------       ----------
       1       Fraud 1
       2       Fraud 2
       3       Fraud 3
       4       Fraud 4
       5       Fraud 5


sql> select * from alarms ;

fraud_id       dealer      count
--------       ------      -----
       1       Deal 1          5
       3       Deal 1          3
       5       Deal 1          4
       1       Deal 2          2
       2       Deal 2          6
       3       Deal 2          1
       4       Deal 2          7
       5       Deal 2          9

我希望将这两个表连接起来，并获得如下输出

dealer      fraud_id        count
------      --------        -----
Deal 1             1            5
Deal 1             2            0
Deal 1             3            3
Deal 1             4            0
Deal 1             5            4
Deal 2             1            2
Deal 2             2            6
Deal 2             3            1
Deal 2             4            7
Deal 2             5            9

基本上，我也想包括来自fraud_types的字段，如果它不存在于alarms表中，则只在输出中显示0。我如何才能做到这一点？请帮帮忙

问候

Answer 1

您可以先使用cross join获取所有组合，然后再使用left outer join

select d.dealer, f.fraud_id, coalesce(cnt, 0)
from (select distinct dealer from fraud_types) d cross join
     fraud_types f left outer join
     (select dealer, fraud_id, count(*) as cnt
      from fraud_types
      group by dealer, fraud_id
     ) df
     on df.dealer = d.dealer and df.fraud_id = f.fraud_id
order by d.dealer, f.fraud_id;

Answer 2

分区的外连接在这样的情况下非常有用：

select a.dealer, f.fraud_id, nvl(a.count,0) count
  from fraud_types f
  left outer join alarms a
      partition by (a.dealer)
      on a.fraud_id = f.fraud_id
 order by a.dealer, f.fraud_id

对于在alarms中找到的每个交易商的值，这将在alarms和fraud_types之间进行外连接。

--

如果alarms表没有(欺诈，交易商)作为键，那么您可以在分区外连接之前执行group by：

select a.dealer, f.fraud_id, nvl(a.count,0) count
  from fraud_types f
  left outer join (
     select fraud_id
          , dealer
          , sum(count) count
       from alarms
      group by fraud_id, dealer
  ) a
      partition by (a.dealer)
      on a.fraud_id = f.fraud_id
 order by a.dealer, f.fraud_id

Answer 3

   select distinct f.fraud_id,dealer,
   (case when f.fraud_id=t.fraud_id then COUNT else 0 end) counts

   from 
   fraud_types f
   left  join
   alarms t
   partition by (dealer)
   on f.fraud_id=t.fraud_id 
   order by dealer