複数のテーブルを使用する時は・・・・

質問

SQLについて教えてくださいぃ～使用しているのは、”Oracle SQL+”です自分はある工場のへっぽこシステム管理者ですが、お上よりSQLを駆使して下記の条件の表を作ってと頼まれました...
<生産>,<不良>,<不良内容>テーブルを使用して表を作りたいのですが・・

<生産>テーブル
日付 　|生産工場No.|機械No. | 商品名 | 生産数 |
2008/3/29 　|　100 　 | A 　 | チョコ | 300 |
2008/3/29 　|　100 　 | A | チョコ | 450 |
2008/3/29　| 100 　 | B | 板チョコ | 600 |

<不良>テーブル
日付 | 生産工場No. | 機械No. | 商品名 | 不良モード |停止時間（分）|
2008/3/29 | 100 | A | チョコ | カケ | 4 |
2008/3/29 | 100 | A | チョコ | カケ | 8 |
2008/3/29 | 100 | A | チョコ | 曲げ | 10 |
2008/3/29 | 100 | A | チョコ | 汚れ | 5 |
2008/3/29 | 100 | B | 板チョコ | カケ | 4 |
2008/3/29 | 100 | B | 板チョコ | カケ | 8 |
2008/3/29 | 100 | B | 板チョコ | キズ | 8 |

<不良モード>テーブル
生産工場　| 不良モード | マシン要因 | 人的要因 |
100 | カケ | ☆ | |
100 | 曲げ | | ☆ |
100 | 汚れ | | ☆ |
　100 | キズ | | ☆ |
100 | 溶け | | ☆ |
100 | 割れ | ☆ | |
100 | ヒビ | ☆ | |

上記３つのテーブルを使って下記の表のように集計したいのです。
↓↓↓

機械No. | 商品名 | 生産数 | カケ停止時間（分）| 人的要因での停止時間（分）|
A | チョコ | 750 | 12 | 15 |
B | 板チョコ | 600 | 15 | 8 |

【条件】
(1)　製造工場が’100’
(2)　日付が’2008/03/29’

各テーブルにおいてのクエリは出来たのですが、結合すると集計が・・・・・・
<生産>にて、
select 機械No.,商品名、sum(生産数) from 生産
where 製造工場='100' and 日付='2008/03/29'
group by 機械No.,商品名 order by 機械No.,商品名

<不良>にて、
select 機械No.,商品名、sum(停止時間) from 不良
where 製造工場='100' and 日付='2008/03/29 and 不良モード='カケ'
group by 機械No.,商品名 order by 機械No.,商品名

<不良>　<不良内容>にて、
select 機械No.,商品名、sum(停止時間) from 不良,不良モード
where 製造工場='100' and 日付='2008/03/29 and 不良モード<>'カケ' and 人的要因='☆'
group by 機械No.,商品名 order by 機械No.,商品名

この３つのテーブルを使って、一覧が一度に出来れば助かるのですが、せめて
<生産>,<不良>のテーブルで機械No.,品名,生産数,カケ停止時間（分）までは出力したのです。
まだ未熟者ですので、皆様の御力を借りれれば幸いです。お願いします
※図の方が判りづらくて申し訳ございません。

回答No.2

3rd_001 · ベストアンサー

考え方

(1)「生産」と「不良」のデータを横展開してカラム数を合わせます。
・「不良」側に「生産数」はないので、定数として0を設定。
・「生産」側には「停止時間」はないので各項目を定数として0を設定。
・「停止時間」は「不良モード」、「マシン要因」、「人的要因」によって集計が
　異なるのでCASEで識別し別カラムとして計上する。

ここで集計に必要なデータを整備します。

(2) ユニオンしたものをキーで集計する。

ジョインだと以下のようなパターンがあるのでユニオンでやっています。

・「生産」にあって「不良」にない場合。
　※全て良品として計上される場合もありえる。
・「不良」にあって「生産」にない場合。
　※全て不良品として計上される場合もありえる。

実際のデータには「指示No」とか「工程」等の考え方があると思います。
私が携わったシステムにはありました。
「仕掛」とか「工程在庫」とか「工程歩留」とか「一貫歩留」とか「計上月」とか…。

「見える化」をやり始めると、各データ間の整合性が取れなかったりするので大変ですよ。

お礼

またまた回答が遅くなってすみません・・・・
わざわざ考え方を本当にありがとうございます☆とても勉強になります。
3rd_001さんがおっしゃる通り、製造工程の「見える化」です。
最近この世界へ足を踏み入れたので、このような考え方を教えて頂ける事がとてもありがたいです。初心者なので色々とお聞きすると思いますので、もしまた見つけましたら何卒宜しくお願いします。

回答No.1

3rd_001

”製造の見える化”の案件で似たようなことをやりました。
他には「歩留」とか「リードタイム」とか作りました。
※Oracle11gで確認

create table "生産"
(
"日付" varchar2(10),
"生産工場No" varchar2(10),
"機械No" varchar2(10),
"商品名" varchar2(50),
"生産数" number(10)
);

insert into 生産 values('2008/3/29','100','A','チョコ',300);
insert into 生産 values('2008/3/29','100','A','チョコ',450);
insert into 生産 values('2008/3/29','100','B','板チョコ',600 );

create table "不良"
(
"日付" varchar2(10),
"生産工場No" varchar2(10),
"機械No" varchar2(10),
"商品名" varchar2(50),
"不良モード" varchar2(50),
"停止時間" number(10)
);
insert into 不良 values('2008/3/29','100','A','チョコ','カケ',4);
insert into 不良 values('2008/3/29','100','A','チョコ','カケ',8);
insert into 不良 values('2008/3/29','100','A','チョコ','曲げ',10);
insert into 不良 values('2008/3/29','100','A','チョコ','汚れ',5);
insert into 不良 values('2008/3/29','100','B','板チョコ','カケ', 4);
insert into 不良 values('2008/3/29','100','B','板チョコ','カケ', 8);
insert into 不良 values('2008/3/29','100','B','板チョコ','キズ',8);

create table "不良モード"
(
"生産工場No" varchar2(10),
"不良モード" varchar2(50),
"マシン要因" varchar2(50),
"人的要因" varchar2(50)
);

insert into 不良モード values('100','カケ', '☆', '');
insert into 不良モード values('100','曲げ', '', '☆');
insert into 不良モード values('100','汚れ', '', '☆');
insert into 不良モード values('100','キズ', '', '☆');
insert into 不良モード values('100','溶け', '', '☆');
insert into 不良モード values('100','割れ', '☆', '');
insert into 不良モード values('100','ヒビ', '☆', '');

select
x."機械No",
x."商品名",
sum(x."生産数") AS 生産数,
sum(x."カケ停止時間") AS カケ停止時間,
sum(x."マシン要因停止時間") AS マシン要因停止時間,
sum(x."人的要因停止時間") AS 人的要因停止時間
from
(
select a."機械No",
a."商品名",
a."生産数" AS 生産数,
0 カケ停止時間,
0 マシン要因停止時間,
0 人的要因停止時間
from "生産" a
where a."生産工場No"='100'
and a."日付"='2008/3/29'
union all
select b."機械No",
b."商品名",
0 AS 生産数,
case when b."不良モード" = 'カケ' then b.停止時間 else 0 end AS カケ停止時間,
case when c."マシン要因" = '☆' then b.停止時間 else 0 end AS マシン要因停止時間,
case when c."人的要因" = '☆' then b.停止時間 else 0 end AS 人的要因停止時間
from "不良" b
left outer join "不良モード" c
on b."生産工場No" = c."生産工場No"
and b."不良モード" = c."不良モード"
where b."生産工場No"='100'
and b."日付"='2008/3/29'
) x
group by
x."機械No",
x."商品名"
order by
x."機械No",
x."商品名"
;

機械No 商品名 生産数 カケ停止時間 マシン要因停止時間 人的要因停止時間
-------------------- ---------- ---------- ------------ ------------------ ----------------
A チョコ 750 12 12 15
B 板チョコ 600 12 12 8

お礼

回答が遅くなりすみません・・・
大変勉強になりました!!大分時間が掛かったのですが、
教えて頂いた、PRGの方を応用して思った通りの結果が、
得られました♪ありがとうございました。
”0 as カケ停止時間”は、union allの際架空のcolumをあけておくんですね☆それから欲しい一覧をselectするとは・・・あとcase whenの使い方も目からウロコでした♪
そのような手法があることとは、まだまだ勉強不足でした・・・
大分参考になりました。ありがとうございました。

補足

回答の方遅くなり大変申し訳ございませんでした・・・本当にありがとうございます!!とてもとても勉強になりました♪わざわざ確認してまで頂いて。まだまだ勉強不足でわからない点があるのですが、”0 カケ停止時間”とは、どのような事なのでしょうか。教えて頂いた上にお聞きするのは大変申し訳ないのですが、宜しくお願いします。