Puzzle51 - SQLパズル演習

問題の要約

予算と実支出を表す二つのテーブルがある。
これらは一体多の関係で、一つの予算に対する支払いが複数回に分割されることがある。

それぞれのスキーマは以下の通り

• Budgeted(task integer, category integer, est_cost DECIMAL(8,2))
• Actual(voucher integer, task integer, act_cost)

カテゴリ別に予算と実支出を比較したい。つまりは以下のような結果が欲しい。

category	estimated	spent
9100	121.00	77.00
9200	19.00	15.00

のだが、問題文にある問い合わせだと下記のようになりうまくいかない。

category	estimated	spent
9100	321.00	77.00
9200	30.00	15.00

うまくいく方法は？

ゼミ中にあったコメントなど

• 別解　：　結合をする前にActualテーブルをタスクごとにまとめちゃえばよいのでは？

select category,sum(est_cost) as estimated,sum(act_cost_sum) as spent 
from Budgeted B 
    LEFT OUTER JOIN (
      select voucher,task,sum(act_cost) as act_cost_sum 
      from Actual group by task) A 
     ON B.task = A.task 
group by category;

+----------+-----------+-------+
| category | estimated | spent |
+----------+-----------+-------+
|     9100 |    121.00 | 77.00 | 
|     9200 |     19.00 | 15.00 | 
+----------+-----------+-------+
2 rows in set (0.00 sec)

できましたー。