Puzzle74 - SQLパズル演習

問題

fooの数量、barの数量を計算書番号ごとに出す

 accounts    foo　　　　　　　　　bar　　　　　　　　　答え
  acct_nbr   acct_nbr | bar_qty  acct_nbr | bar_qty      |foo_qty|bar_qty
-----------   --------+--------  ---------+---------   --------------------
       1            2 |     160         2 |     160　→ 1|   10  |  0  
       2            3 |     320         3 |     320     2|   60  |  160
       3            3 |     640         3 |     640     3|   80  |  961
       4            3 |       1         3 |       1     4|   0   |  0

サンプルコード↓これ以外を考えよう♪

 select a.acct_nbr,
        coalesce(f.foo_qty,0) as foo_qty_tot,
        coalesce(b.bar_qty,0) as bar_qty_tot
 from accounts as a
      left outer join
      (select acct_nbr,sum(foo_qty) as foo_qty
        from foo
      group by acct_nbr) as f
      on f.acct_nbr = a.acct_nbr
      left outer join
       (select acct_nbr,sum(bar_qty) as bar_qty
        from bar
       group by acct_nbr) as b
      on f.acct_nbr = b.acct_nbr;

• COALESCE（式１,式２,式３,...）
  • 式１がNULL以外→終了、式１がNULL→式２実行。
  • 式２がNULL→式３実行、引数で指定された式がNULL以外の値を返すまで続く
  • 全ての式がNULL→NULLをCOALESCEの結果として返す

yukaさんの回答

fumikoの回答

考え

• accountsのacct_nbrごとに
  • fooからacct_nbrが同じときだけfoo_qtyを足していく
  • barからacct_nbrが同じときだけbar_qtyを足していく

SQL

 select ac.acct_nbr,
        sum(distinct case when ac.acct_nbr = foo.acct_nbr then foo_qty else 0 end),
        sum(distinct case when ac.acct_nbr = bar.acct_nbr then bar_qty else 0 end)
 from accounts as ac,foo,bar
 group by ac.acct_nbr

結果

  acct_nbr | sum | sum
 ----------+-----+-----
         1 |  10 |   0
         2 |  60 | 160
         3 |  80 | 961
         4 |   0 |   0