C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[2]: SQLについての質問です

(過去ログ 85 を表示中)

[トピック内 11 記事 (1 - 11 表示)]　 << 0 >>

■50040 / inTopicNo.1)

　SQLについての質問です

▼■

□投稿者/ 森塚 (3回)-(2010/05/27(Thu) 01:40:11)

分類:[データベース全般]　

下記のようなテーブルがあります。

テーブルA
---------
型番 PK
値段
色

テーブルB
---------
型番 PK
製造場所 PK
製造番号 PK
売り切れ判定

テーブルAに型番とテーブルBの型番は結びついており
テーブルAを主体にして
型番、値段、色、売り切れ判定を取得したいのですが
売り切れ判定の判断方法が以下のようになっています。

①テーブルBのデータは一つの型番に対して複数の製造場所と製造番号のレコードを持つとします。
②テーブルBのレコードで同じ製造場所で複数のレコードが存在し、そのうち１つでも売り切れ判定が売り切れになっているレコードが存在したら売り切れになります。全部が在庫ありの場合は在庫ありと判断します。
③②の判定の上で複数の製造場所を判定し一つでも在庫ありの場所があった場合は在庫ありと総合判断します。

例
テーブルB
型番、製造場所、製造番号、売り切れ判定
A100、青森、10、売り切れ
A100、青森、20、在庫あり
A100、山形、30、売り切れ
A100、山形、40、在庫あり

の場合には青森、山形とも片方が売り切れなので青森、山形共に売り切れのとなります。なので総合的には売り切れです。

型番、製造場所、製造番号、売り切れ判定
A100、青森、10、売り切れ
A100、青森、20、在庫あり
A100、山形、30、在庫あり
A100、山形、40、在庫あり

の場合には在庫は青森は売り切れですが、山形は2レコードとも在庫ありなので山形は在庫ありとなり総合的には在庫ありとなります。

この判定結果を踏まえた値で
型番、値段、色、売り切れ判定
を取得するにはどのようにすればよいのでしょうか？
環境はoracle10gです。

JOINとかでできるものなのでしょうか。。。

質問初心者なので足りない情報等がありましたら教えていただければ幸いです。
よろしくお願いします。

引用返信 編集キー/

■50041 / inTopicNo.2)

　Re[1]: SQLについての質問です

▲▼■

□投稿者/ かたぎり (26回)-(2010/05/27(Thu) 02:38:08)

ようするに、テーブルＢを「型番」「製造場所」でまとめて、
「売り切れ」レコードが全くない「製造場所」を持つ「型番」を
テーブルＡにおいて「在庫あり」とする、
ということだと思うので、

文言での設計になりますが

テーブルBを型番と製造場所でグルーピングして「売り切れ」のデータを抽出する

→型番、製造場所の内部ワークテーブル「売り切れＢ」が出来上がる

→テーブルＡの型番　Except 「売り切れＢ」の型番の SELECT　で「在庫ありＡ」が出来上がる

→テーブルＡと「在庫ありＡ」をJOINして、JOINできれば「在庫あり」なければ「売り切れ」でSELECT

１文SQLで書けるとおもいますが、長いかも。

型番と製造番号はPKなので、遅くはならないと思いますけれどもね

ところで「色」列はテーブルＢにないけれども、関係は大丈夫なのかしら？

引用返信 編集キー/

■50045 / inTopicNo.3)

　Re[2]: SQLについての質問です

▲▼■

□投稿者/ 中博俊 (1407回)-(2010/05/27(Thu) 08:59:44)

> 型番と製造番号はPKなので、遅くはならないと思いますけれどもね

> テーブルBを型番と製造場所でグルーピングして「売り切れ」のデータを抽出する

グルーピングしてるからインデックス効かなくなるので、おそくなるかもね。
それより先にかけ算してから絞り込んだ方が早いかも。
まぁ状況に合わせてってかんじー

引用返信 編集キー/

■50096 / inTopicNo.4)

　Re[1]: SQLについての質問です

▲▼■

□投稿者/ てきとう (4回)-(2010/05/28(Fri) 00:31:25)

■No50040 (森塚さん) に返信
> 下記のようなテーブルがあります。
>
> テーブルA
> ---------
> 型番 PK
> 値段
> 色
>
> テーブルB
> ---------
> 型番 PK
> 製造場所 PK
> 製造番号 PK
> 売り切れ判定

こんなかんじでいけるのでは？
売り切れ判定の値　'0':在庫あり '1':売り切れ　としています

テーブルAとテーブルBから在庫ありの型番のみ抽出した行をOUTER JOIN
　型がNULLは売り切れ

型番、製造場所のグループで
MAX,MINの値が相違＝＞在庫ありと売り切れ混在＝＞売りきれ
MAX,MINの値が一致＝＞売り切れ　　　　　　　＝＞売りきれ
MAX,MINの値が一致＝＞在庫あり　　　　　　　＝＞在庫あり　ここを対象

select
テーブルA.型番,
テーブルA.値段,
テーブルA.色,
nvl2(t.型番,'在庫あり','売り切れ') 判定
from
テーブルA
left outer join
( select 型番,MAX(製造場所)
from (
select 型番,製造場所
from テーブルB group by 型番,製造場所
having max(売り切れ判定)=min(売り切れ判定) and max(売り切れ判定)='0'
) group by 型番
)t
on (テーブルA.型番=t.型番)

SQL関係は別の掲示版に投稿したほうが適切が回答がえられると思います

引用返信 編集キー/

■50097 / inTopicNo.5)

　Re[3]: SQLについての質問です

▲▼■

□投稿者/ 森塚 (4回)-(2010/05/28(Fri) 00:56:18)

ご解答ありがとうございます。
大変助かります。

考えてみましたがこんな感じでしょうか？

SELECT テーブルA.型番,テーブルA.値段,テーブルA.色,”ここの判定がわかりません”
　FROM テーブルA
　　JOIN
　(SELECT 型番 FROM テーブルA
　　EXCEPT
　SELECT 型番 FROM (型番、製造番号 FROM テーブルB
　　　WHERE 売り切れフラグ=売り切れ GROUP BY 型番、製造番号) 売り切れB) 在庫ありA

全然検討違いな事かいてたらすみません。。。

「ここでJOINできれば」での実際の売り切れ判定の現し方がわかりません。

もしも、よろしければSQLで教えていただけるととても助かります。

引用返信 編集キー/

■50098 / inTopicNo.6)

　Re[2]: SQLについての質問です

▲▼■

□投稿者/ 森塚 (5回)-(2010/05/28(Fri) 00:59:12)

■No50096 (てきとうさん) に返信
> ■No50040 (森塚さん) に返信
>>下記のようなテーブルがあります。
>>
>>テーブルA
>>---------
>>型番 PK
>>値段
>>色
>>
>>テーブルB
>>---------
>>型番 PK
>>製造場所 PK
>>製造番号 PK
>>売り切れ判定
>
> こんなかんじでいけるのでは？
> 売り切れ判定の値　'0':在庫あり '1':売り切れ　としています
>
> テーブルAとテーブルBから在庫ありの型番のみ抽出した行をOUTER JOIN
> 　型がNULLは売り切れ
>
> 型番、製造場所のグループで
> MAX,MINの値が相違＝＞在庫ありと売り切れ混在＝＞売りきれ
> MAX,MINの値が一致＝＞売り切れ　　　　　　　＝＞売りきれ
> MAX,MINの値が一致＝＞在庫あり　　　　　　　＝＞在庫あり　ここを対象
>
> select
> テーブルA.型番,
> テーブルA.値段,
> テーブルA.色,
> nvl2(t.型番,'在庫あり','売り切れ') 判定
> from
> テーブルA
> left outer join
> ( select 型番,MAX(製造場所)
> from (
> select 型番,製造場所
> from テーブルB group by 型番,製造場所
> having max(売り切れ判定)=min(売り切れ判定) and max(売り切れ判定)='0'
> ) group by 型番
> )t
> on (テーブルA.型番=t.型番)
>
> SQL関係は別の掲示版に投稿したほうが適切が回答がえられると思います
>

てきとうさん、ありがとうございます
自分がちょうど返答を書いている間に回答してくれていたんですね^^;

試してみます。

引用返信 編集キー/

■50343 / inTopicNo.7)

　Re[2]: SQLについての質問です

▲▼■

□投稿者/ 森塚 (6回)-(2010/06/04(Fri) 01:01:08)

■No50096 (てきとうさん) に返信
> ■No50040 (森塚さん) に返信
>>下記のようなテーブルがあります。
>>
>>テーブルA
>>---------
>>型番 PK
>>値段
>>色
>>
>>テーブルB
>>---------
>>型番 PK
>>製造場所 PK
>>製造番号 PK
>>売り切れ判定
>
> こんなかんじでいけるのでは？
> 売り切れ判定の値　'0':在庫あり '1':売り切れ　としています
>
> テーブルAとテーブルBから在庫ありの型番のみ抽出した行をOUTER JOIN
> 　型がNULLは売り切れ
>
> 型番、製造場所のグループで
> MAX,MINの値が相違＝＞在庫ありと売り切れ混在＝＞売りきれ
> MAX,MINの値が一致＝＞売り切れ　　　　　　　＝＞売りきれ
> MAX,MINの値が一致＝＞在庫あり　　　　　　　＝＞在庫あり　ここを対象
>
> select
> テーブルA.型番,
> テーブルA.値段,
> テーブルA.色,
> nvl2(t.型番,'在庫あり','売り切れ') 判定
> from
> テーブルA
> left outer join
> ( select 型番,MAX(製造場所)
> from (
> select 型番,製造場所
> from テーブルB group by 型番,製造場所
> having max(売り切れ判定)=min(売り切れ判定) and max(売り切れ判定)='0'
> ) group by 型番
> )t
> on (テーブルA.型番=t.型番)
>
> SQL関係は別の掲示版に投稿したほうが適切が回答がえられると思います
>

返信遅くなってすみません。

理解しようとしたのですが

> ( select 型番,MAX(製造場所)

のMAX(製造場所)とはなんのために行っているんでしょうか？

あとすみません。忘れていたのですが判定でテーブルBに該当の型番の
レコードが存在しないときは判定として対象外とするにはどのようにしたらよろしいでしょうか？

申し訳ありませんがよろしくおねがいします。

引用返信 編集キー/

■50350 / inTopicNo.8)

　Re[3]: SQLについての質問です

▲▼■

□投稿者/ ちゃっぴ (24回)-(2010/06/04(Fri) 03:04:24)

>> ( select 型番,MAX(製造場所)

> のMAX(製造場所)とはなんのために行っているんでしょうか？

GROUP BY 使っていて集計条件に [製造場所] は含まれていないですからね。

> 判定でテーブルBに該当の型番のレコードが存在しないときは判定として対象外とするにはどのようにしたらよろしいでしょうか

Table B を SLECECT しているところの WHERE でそういう条件を加えればいいかと。

ちゃんと読んでいないのであしからず。

引用返信 編集キー/

■50374 / inTopicNo.9)

　Re[1]: SQLについての質問です

▲▼■

□投稿者/ 裕猫 (120回)-(2010/06/04(Fri) 16:13:37)

■No50040 (森塚さん) に返信
> テーブルA
> ---------
> 型番 PK
> 値段
> 色
テーブルAの型番を順次読むループを作る。そのループの中で

> テーブルB
> ---------
> 型番 PK
> 製造場所 PK
> 製造番号 PK
> 売り切れ判定
テーブルBを型番、製造場所順のキーでORACLEデータを読むループを作る
フラグを1つ用意して0にしておき売り切れならばフラグ1にするようにする。製造場所が変わったときフラグ0なら在庫有りなので、ループを抜けテーブルAのループに戻り、全件の在庫を調べる。
こんな感じですか。sql文はデータを取得するだけにしてメインのプログラムで判別させるのがいいんではないかなと思います。思い描いているのと違っていたらごめんなさい。

引用返信 編集キー/

■50377 / inTopicNo.10)

　Re[2]: SQLについての質問です

▲▼■

□投稿者/ はつね (1279回)-(2010/06/04(Fri) 17:37:44)

■No50040 (森塚 さん) に返信

各地の判定はこんな感じ？

SELECT A.型番,A.値段,A.色,B.製造場所,DECODE(B.在庫件数=B.件数,'在庫あり','在庫なし')
FROM (
SELECT 型番,製造場所,SUM(DECODE(売り切れ判定='売り切れ','0','1')) AS 在庫件数,COUNT(*) AS 件数
  FROM テーブルＢ
 GROUP BY 型番,製造番号
) B,テーブルＡ A
WHERE A.型番=B.型番(+)

全国判定は

SELECT A.型番,A.値段,A.色,B.製造場所,DECODE(B.在庫件数=0,'在庫あり','在庫なし')
FROM (
SELECT 型番,製造場所,SUM(DECODE(売り切れ判定='売り切れ','0','1')) AS 在庫件数
  FROM テーブルＢ
) B,テーブルＡ A
WHERE A.型番=B.型番(+)

引用返信 編集キー/

■50384 / inTopicNo.11)

　Re[3]: SQLについての質問です

▲▼■

□投稿者/ てきとう (6回)-(2010/06/04(Fri) 23:56:19)

2010/06/07(Mon) 06:01:24 編集(投稿者)
2010/06/05(Sat) 22:13:42 編集(投稿者)
2010/06/05(Sat) 00:55:58 編集(投稿者)
2010/06/05(Sat) 00:52:00 編集(投稿者)
2010/06/05(Sat) 00:51:46 編集(投稿者)
2010/06/05(Sat) 00:51:16 編集(投稿者)

■No50343 (森塚さん) に返信

>
> あとすみません。忘れていたのですが判定でテーブルBに該当の型番の
> レコードが存在しないときは判定として対象外とするにはどのようにしたらよろしいでしょうか？
>
> 申し訳ありませんがよろしくおねがいします。
最初の投稿で「テーブルAを主体にして」とありますが
テーブルAとテーブルBの項目を表示する場合３パターンあります
どのパターンに該当しますか？

(1)テーブルAのみ存在する型番
　処理対象　在庫判定項目はNULL　　　　　　＝＞外部結合
　対象外　　　　　　　　　　　　　　　　　＝＞(3)
(2)テーブルBのみ存在する型番
　処理対象　テーブルAに関する項目はNULL　＝＞外部結合
　対象外　　　　　　　　　　　　　　　　　＝＞(3)
(3)テーブルAとテーブルBの両方に存在する型番
　　＝＞Inner join

現行SQLではテーブルBに存在しない型番は売り切れ判定になりますが
テーブルAの型番の判定を売り切れ、在庫あり、対象外、の３種類にするということですか？

select 型番 from テーブルA minus select 型番 from テーブルB
でテーブルBに存在しない型番を作成します

型番、場所の単位で在庫ありを抽出する理由は
型番、場所の単位で在庫ありの場合は型番単位でも在庫ありになるためです

select
テーブルA.型番,
テーブルA.値段,
テーブルA.色,
CASE WHEN h.対象外型番 IS NOT NULL THEN '対象外' ELSE nvl2(t.型番,'在庫あり','売り切れ') END
from
テーブルA
left outer join
(
select 型番 as 対象外型番 from テーブルA minus select 型番 from テーブルB
)h
on (テーブルA.型番=h.型番)
left outer join
( select 型番
from (
select 型番,製造場所
from テーブルB group by 型番,製造場所
having min(売り切れ判定)='0' and max(売り切れ判定)='0'
) group by 型番
)t
on (テーブルA.型番=t.型番 )

引用返信 編集キー/

トピック内ページ移動 / << 0 >>

このトピックに書きこむ

過去ログには書き込み不可

- Child Tree -