mysqlで複数のテーブルを1つの同一キーを元にして集計して結合する方法です。
たいていのデータベースは、ユニークなキーを持つマスターテーブルを用意して、そのキーを使っていろんなデータを表現するテーブルを作成していきます。
例えば、社員マスターテーブルを基準にして、各社員の売上実績テーブルや交通費実績テーブルを作ります。
社員マスターテーブルは、社員IDでユニークになっています。
対して、実績テーブルは、社員IDが複数回現れたり、1回も現れない社員IDがあったりします。
このデータから、社員別の売上合計と交通費合計の一覧を求める方法です。
集計
まず、社員売上実績データと社員交通費実績データを社員ID単位で集計するSQL文を作成します。
[A] select 社員ID, sum(売上) as sales_sum from 社員売上実績 group by 社員ID;
[B] select 社員ID, sum(交通費) as travels_sum from 社員交通費実績 group by 社員ID;
group by で集計単位を指定するとデータが集計単位で圧縮されてsum()やcount()などで集計計算できます。
結合
次に、マスターテーブルを元に結合します。結合にはleft joinを使用します。（左側のデータを基準にして結合し、右側のデータの有無に関係なく左側のデータを全件出力）
select master.社員ID, master.名前, sales.sales_sum, travels.travels_sum from 社員マスター as master
left join ( [A] ) as sales on master.社員ID = sales.社員ID
left join ( [B] ) as travels on master.社員ID = travels.社員ID
[A], [B] の部分には先のSQL文を入れます。注意点は、[A]や[B]などleft joinで結合するSQL文にasで別名をつける必要があることです。
[A], [B]にSQL文を代入した実際のSQL文は次のようになります。select master.社員ID, master.名前, sales.sales_sum, travels.travels_sum from 社員マスター as master left join (select 社員ID, sum(売上) as sales_sum from 社員売上実績 group by 社員ID) as sales on master.社員ID = sales.社員ID left join (select 社員ID, sum(交通費) as travels_sum from 社員交通費実績 group by 社員ID ) as travels on master.社員ID = travels.社員ID
これで、次のように集計結果が得られます。
NULLを0に
データがない部分は0ではなく、NULLと出力されるので、このデータを元に別の計算を行いたい場合などは不便です。そこで、NULLの部分を0に変えて表示します。それには、IFNULL()関数を使います。
[A] select 社員ID, IFNULL(sum(売上), 0) as sales_sum from 社員売上実績 group by 社員ID;
[B] select 社員ID, IFNULL(sum(交通費), 0) as travels_sum from 社員交通費実績 group by 社員ID;
最終的なSQL文は次のようになります。
select master.社員ID, master.名前, sales.sales_sum, travels.travels_sum from 社員マスター as master left join (select 社員ID, IFNULL(sum(売上), 0) as sales_sum from 社員売上実績 group by 社員ID) as sales on master.社員ID = sales.社員ID left join (select 社員ID, IFNULL(sum(交通費), 0) as travels_sum from 社員交通費実績 group by 社員ID ) as travels on master.社員ID = travels.社員ID
これで、次のように集計結果が得られます。
