副問い合わせとGROUPの組み合わせ・・・

質問

プロジェクトA　月間勤務状況一覧
氏名　１月　２月　３月　合計
山田　２０　１０　１０　４０
川村　１０　１０　１０　３０
大沢　２０　２０　２０　６０

上記のような一覧をつくりたいのですが、
STAFF表（社員情報）とLABOR表（勤務情報）からSQL一発で
情報を取り出すためのSQL作成に四苦八苦しています。

人別に情報を取り出すには、GROUPを使えばよいというところまではわかるのですが、
月ごとの情報を取得するために副問い合わせを用いる際に、
人別に情報を取得しようとすると
副問い合わせ文内で社員を指定する必要が出てきてしまいます。
すると社員を纏めて表示するためのGROUPを使う意味がそもそもなくなってしまいます。

以上のような点から、SQLを作る事ができないでおります。
どのように記述すれば宜しいでしょうか？アドバイス頂ければ大変助かります。

回答No.3

chukenkenkou · ベストアンサー

試しにSQLを作ってみました。

create table STAFF
(ID smallint,
名前 varchar(10));
create table LABOR
(ID smallint,
社員ID smallint,
年月日 date,
時間数 smallint,
プロジェクトID smallint);

insert into STAFF values(101,'川口');
insert into STAFF values(111,'宮本');
insert into STAFF values(123,'中田英');

insert into LABOR(社員ID,年月日,時間数) values(101,'2006-01-05',8);
insert into LABOR(社員ID,年月日,時間数) values(101,'2006-02-01',10);
insert into LABOR(社員ID,年月日,時間数) values(101,'2006-02-15',5);
insert into LABOR(社員ID,年月日,時間数) values(101,'2006-03-01',8);
insert into LABOR(社員ID,年月日,時間数) values(101,'2006-03-03',10);
insert into LABOR(社員ID,年月日,時間数) values(101,'2006-03-10',12);
insert into LABOR(社員ID,年月日,時間数) values(111,'2006-01-10',5);
insert into LABOR(社員ID,年月日,時間数) values(111,'2006-01-15',10);
insert into LABOR(社員ID,年月日,時間数) values(111,'2006-01-31',5);
insert into LABOR(社員ID,年月日,時間数) values(111,'2006-02-10',8);
insert into LABOR(社員ID,年月日,時間数) values(111,'2006-02-20',8);
insert into LABOR(社員ID,年月日,時間数) values(111,'2006-03-20',10);
insert into LABOR(社員ID,年月日,時間数) values(123,'2006-01-20',8);
insert into LABOR(社員ID,年月日,時間数) values(123,'2006-01-21',8);
insert into LABOR(社員ID,年月日,時間数) values(123,'2006-01-22',8);
insert into LABOR(社員ID,年月日,時間数) values(123,'2006-03-03',5);
insert into LABOR(社員ID,年月日,時間数) values(123,'2006-03-04',5);
insert into LABOR(社員ID,年月日,時間数) values(123,'2006-03-05',5);

select
x.名前,
coalesce(sum(case when date_part('month',年月日)=1 then 時間数 end),0) as １月,
coalesce(sum(case when date_part('month',年月日)=2 then 時間数 end),0) as ２月,
coalesce(sum(case when date_part('month',年月日)=3 then 時間数 end),0) as ３月,
coalesce(sum(case when date_part('month',年月日) between 1 and 3 then 時間数 end),0) as 合計
from STAFF as x,LABOR as y
where x.ID=y.社員ID
group by x.名前
order by x.名前
;

お礼

chukenkenkou様

ありがとうございました！
まったく知らなかったSQLです。
勉強になります。
早速、こちらを参考につくってみます。

＊なお「宮本」が何故か「川口」と名寄せされて、一緒に集計されてしまうのですが…。
英語名に変えてみたら、正しく集計されました。

　　　1月　2月　3月　合計
川口　　28　31　40　99
中田英　24　 0　15　39

補足

名寄せされてしまう理由は分かりませんでしたが、
STAFFの一意識別子（ID）でGROUPすれば、正しく集計されました。

どうもありがとうございました！

回答No.2

chukenkenkou

>２）LABOR表（勤務情報）
>ID
>社員ID（FK：STAFF.ID）
>年月日
>時間数
>プロジェクトID（FK：PROJECT.ID）

「月間勤務状況一覧」で集計しているのは、日数のように見えます。
時間数は無視して、勤務日数でだけ集計できればいいのですか？

こういう質問をする場合は、
(1)基の各表の列構成
(2)基の各表に入っているデータ例
(3)得たい検索結果
を示してくれないと、適切な回答ができません。

(3)は、質問で示されています。
(1)は、#1さんへの補足説明で示されました。

(2)は、示せませんか？

補足

chukenkenkou様、回答有り難うございます＆言葉足らずで申し訳ありません。
下記の通り回答させていただきます。

・「月間勤務状況一覧」で集計しているのは、時間数です。勤務時間数は日によって異なるため、月ごとに時間数を集計します。

・表に入っているデータは以下の通りです。

１）STAFF表（社員情報）
ID：100
名前：山田太郎

２）LABOR表（勤務情報）
ID：200
社員ID（FK：STAFF.ID）：100
年月日：2006/01/03
時間数：8
プロジェクトID（FK：PROJECT.ID）：300

３）PROJECT表（プロジェクト情報）
ID：300
プロジェクト名：ヘルスケア事業

以上、他にご不明な点がありましたらご指摘頂ければ幸いです。

回答No.1

ipsum11

STAFF表（社員情報）とLABOR表（勤務情報）の構成がわかりません．．．

補足

ご指摘有り難うございます＆言葉足らずで申し訳ありません。

テーブルの構成ですが大まかに言って下記の通りです：

１）STAFF表（社員情報）
ID
名前

２）LABOR表（勤務情報）
ID
社員ID（FK：STAFF.ID）
年月日
時間数
プロジェクトID（FK：PROJECT.ID）

３）PROJECT表（プロジェクト情報）
ID
プロジェクト名

以上の通りです。
もし説明不足等ありましたらご指摘頂ければ幸いです。
宜しくお願い致します。