【帰ってきた】SQL質疑応答スレ 4問目

　皆様のお力をお貸しください。

　下記3テーブルがSQL Server 2005 Expressをインストールしたテーブル上にあります。

[製品マスタ]
製品名/生産親品番/製品タイプ/シリーズ/電圧記号/容量記号/特性1/特性2/特性3/サイズ記号/ユーザーコード/加工記号/社内管理識別記号/サイズ1/サイズ2
--------------------------------------------------------------------------------------------------------------------------
AAB1A100AAA/AAB1A010AAA/A/AB/1A/100/A/A/A/////1/1
AAB1A100AAA/AAB1A010AAA/A/AB/1A/100/A/A/A///TA//1/1
AAB1A100AAA　　　　　　A/AAB1A100AAA　　　　　　A/A/AB/1A/100/A/A/A////A/1/1
AAB1A100AAA1TA　　　A/AAB1A100AAA　　　　　　A/A/AB/1A/100/A/A/A///TA /A/1/1
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　・
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　・

[注文テーブル]
注文番号/ 注文品番/分納番号/数量
-----------------------------
1/AAB1A010AAA/1/10
1/AAB1A010AAA/2/10
2/AAB1A010AAA1TA/1/3
3/AAB1A010AAA1TA　　　A/1/10
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　・
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　・

[進捗管理テーブル](工程管理コードが1000以上は2次加工品。それ以下は半製品)
製造日番/生産品番/工程管理コード/在庫数/良品数　
------------------------------------------
12345-03/AAB1A010AAA/1220/15/
12345-01/AAB1A010AAA1TA/1220/1/
23456-03/AAB1A010AAA/1100//5
23456-02/AAB1A010AAA1TA/1100//1
23456-01/AAB1A010AAA1TA　　　A/1100//2
45678-00/AAB1A010AAA/990//30
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　・
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　・

そこから下記のような

注文番号/ 注文品番/分納番号/数量/出荷予定製造日番
---------------------------------------------
1/AAB1A010AAA/1/20/12345-03
1/AAB1A010AAA/2/10/12345-03
1/AAB1A010AAA/2/10/23456-03
2/AAB1A010AAA1TA/1/3/12345-01
2/AAB1A010AAA1TA/1/3/23456-02
2/AAB1A010AAA1TA/1/3/45678-00
3/AAB1A010AAA1TA　　　A/1/10/23456-01
3/AAB1A010AAA1TA　　　A/1/10/45678-00
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　・
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　・

結果を取り出したいのですが、どのようなSQLを書いてよいものかわかりません。
もし、SQLだけで不可能なら、どこまでをSQL上で処理しますか？

ご指導のほどよろしくお願いいたします。

>>391
製品マスタにプライマリキーのような物が見あたらね。
-- 複合で出せないわけではなさそうだが。
いったいマスタテーブルと他のテーブルとをどう関連付けりゃいいのやら。
出力例の1行目の数量が20になってのもｲﾏｲﾁ不明。
なんか、マスタの親品番を再帰的に掘っているようにも見えるが、再帰するときの条件も不明瞭。

設計からやり直した方がよだそうだな。

>>392殿

先ほどは各テーブルの要点になりそうなところだけを抽出したため、こんな感じになってしまいました。

実際のテーブルは以下のとおりです。


[注文テーブル]注文番号(PK)/ 注文品番/受注数量/注残数量/...
　　　　　　　　　　　　　└───┐
[引当基本情報テーブル]注文番号(PK)/引当番号(PK)/ 出荷予定品番/引当数量/...
　　　　　　　　　　　　　　　　　　　│　　　　　　　　　│　　　　　　　　　　└──────────┐
　　　　　　　　　　　　　　　　　　　│　　　　　　　　　│　　　　　　　　　　　　　　　　　　　　　　　　 　 │
[引当詳細情報テーブル]注文番号(PK)/引当番号(PK)/明細行番号(PK)/製造日番/...　　　　　│
　　　　　　　　　　　　　　　　┌─────────────────────┘　　　　　　　 │
[進捗管理テーブル]製造日番(PK)/品番/工程管理コード/在庫数/良品数...　　　　　　　　　　　 │
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　│
　　　　　　　　　　┌───────────────────────────────-┘
[製品マスタ]製品名(PK)/生産親品番/製品タイプ/シリーズ/電圧記号/容量記号/特性1/特性2/特性3/サイズ記号/ユーザーコード/加工記号/社内管理識別記号/サイズ1/サイズ2/...

・製品を大きく分けて3つに分けています。(コードの数字が大きいほど完成品に近い)
　・在庫(工程管理コード：1220)
　・2次加工仕掛品(工程管理コード：1200〜1000)
　・1次加工仕掛品(工程管理コード：990〜0)　

・製造日番は各製品の不足合計を生産親品番でまとめて計上することになっています。
　計上した製造日番は"AAAAA-00"で工程管理コード0番から1次加工品工程を進め、工程管理コード990が完了後、2次加工で必要な分を"AAAAA-01"見たいな形で分割し、加工して倉庫へ入庫ます。

・生産親品番は
　　製品タイプ+シリーズ+電圧記号+容量記号+特性1+特性2+特性3+サイズ記号+社内管理識別記号
か
　　製品タイプ+シリーズ+電圧記号+容量記号+特性1+特性2+特性3+ユーザーコード+社内管理識別記号
で求めることができます。（ただし、サイズ記号orユーザーコードと社内管理識別記号との間はスペースで区切り、社内管理識別記号は18文字目になければならないというルールがあります)


　で、現在、どの注文にどの製造日番を割り当てるかを決めるSQL文を考えていたのですが、さっぱり考え付かず、ここに質問させていただいた次第です。

>>393-394
すまんが、そうｺﾞﾁｬｺﾞﾁｬ書かれても、理解する気力ない。
てか、>>391の製品マスタ.製品名のAAB1A100AAAは2行あるように見えるが?

とりあえず 最後の「どの注文にどの製造日番を割り当てるか」だけ注目して
他の余計な物をバッサリと捨てた。参考になるかどうかﾜｶﾗﾝが。

注文テーブル(正確に言うと受注残テーブルかな)
　注文番号 注文品番 注文数量

進捗管理テーブル(まだ在庫がある製造分と、これからの予定分...?)
　製造日番 製造品番 製造数量(在庫分のみ?)

SELECT T3.*,T4.製造日番 FROM
(
SELECT *,
COALESCE((SELECT sum(製造数量) FROM 注文テーブル WHERE 製造日番<T1.製造日番 AND 製造品番=T1.製造品番),0)AS ptotal,
COALESCE((SELECT sum(製造数量) FROM 注文テーブル WHERE 製造日番<=T1.製造日番 AND 製造品番=T1.製造品番),0)AS ntotal
FROM 注文テーブル AS T1
) AS T3
FULL JOIN
(
SELECT *,
COALESCE((SELECT sum(注文数量) FROM 進捗管理テーブル WHERE 注文番号<T2.注文番号 AND 注文品番=T2.注文品番),0)AS ptotal,
COALESCE((SELECT sum(注文数量) FROM 進捗管理テーブル WHERE 注文番号<=T2.注文番号 AND 注文品番=T2.注文品番),0)AS ntotal
FROM 進捗管理テーブル AS T2
)AS T4
ON (T3.製造品番=t4.注文品番 )
WHERE T4.ntotal BETWEEN T3.ptotal AND T3.ntotal
OR T4.ptotal >T3.ptotal
ORDER BY T4.注文番号;

製造日番と注文番号はユニークで昇順に処理していく。
また、納入済み分の処理などを考えるとさらに複雑になるﾖｶｿ。
最後になんだが、SQL鯖使いじゃないので、SQL鯖で動くかどうかもﾜｶﾗﾝ。

あや、一部訂正。
製造数量(在庫分のみ?) → 製造数量(製造済み分は残っている在庫分のみ)
意味通じるかなぁ?

× WHERE T4.ntotal BETWEEN T3.ptotal AND T3.ntotal
○ WHERE T4.ntotal BETWEEN T3.ptotal+1 AND T3.ntotal
まぁ、WHERE (T4.ntotal > T3.ptotal AND T4.ntotal <= T3.ntotal) でもいいんだけど。

で、これ以上突っ込まれて聞かれても俺は答える気力ない。
と、先に断っておく、ｽﾏｿ。

何度もｽﾏｿ。WHRER句のOR以下もおかしいな。一つにまとめて
WHERE (T3.ptotal < T4.ntotal AND T3.ntotal > T4.ptotal)
ORDER BY order_num;

って一つじゃないか。もう、頭まわってね...orz
まだ間違ってるかも。