質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.92%

SQLのSELECT文の条件抽出の質問

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 1,727

cd987456

score 33

SQLのSELECT文でOracle Database抽出しようとしています。
下記の場合の抽出方法が分かりません。

内容は3つのテーブルがあります。
【製品構成テーブル】と【品名テーブル】と【所要テーブル】の3種類です。

【製品構成テーブル】
フィールドは2つで”CODE”と”KCODE”です。
CODEは商品品番、KCODEはその商品の構成部品のイメージです。

CODE      KCODE
A123      C45
A123      D46
A123      E47
B132      G67
B132      C45
B132      E47

【品名テーブル】
フィールドは2つで”CODE”と”NAME”です。
CODEは商品品番、構成部品、全てのコードが入っています。
NAMEはそれぞれの名前です。

CODE      NAME
A123      テレビ
B132      プレーヤー
C45       抵抗
D46       コンデンサ
E47       LED
G67       トランジスタ 

【所要テーブル】
フィールドは2つで”CODE”と”NAME”です。
CODEは商品品番、構成部品、全てのコードが入っています。
SHOYOUはそれぞれの消費数です。
KUBUNは仕様区分です。

CODE    SHOYOU    KUBUN
A123    100    A
A123    10    J
A123    20    T
B132    66    J
B132    50    J
C45    300    A
C45    200    B
D46    400    J
E47    500    Q
G67    900    T
G67    400    F

★それぞれのテーブルを結合し下記のように抽出したいです。
分からない事は"SHOYOU"と"KUBUN"繋げ方なのですが、
条件があります。
CODEに対するKUBUN は『J』か『T』のみを抽出、さらにその中でSHOYOUの最大値を抽出したいです。

CODE    NAME       KCODE    NAME      SHOYOU    KUBUN
A123    テレビ       C45    抵抗       10    J
A123    テレビ       D46    コンデンサ  10    J
A123    テレビ       E47    LED       10    J
A123    テレビ       C45    抵抗       20    T
A123    テレビ       D46    コンデンサ  20    T
A123    テレビ       E47    LED      20    T
B132    プレーヤー  G67    トランジスタ  66    J
B132    プレーヤー  C45    抵抗          66    J
B132    プレーヤー  E47    LED           66    J

品名テーブルを別名でH1、H2としています。

SELECT 製品構成テーブル.CODE , H1.NAME , 製品構成テーブル.KCODE , H2.NAME, 所要テーブル.SHOYOU, 所要テーブル.KUBUN
FROM 製品構成テーブル
LEFT JOIN 品名テーブル H1 ON 製品構成テーブル.CODE = H1.CODE
LEFT JOIN 品名テーブル H2 ON 製品構成テーブル.KCODE = H2.CODE
LEFT JOIN 所要テーブル ON 製品構成テーブル.CODE = 所要テーブル.CODE

VBAのADODB.ConnectionオブジェクトとSQL文を使ってエクセルにデータ抽出しようとしています。
実際のコードとテーブル名を記載します。 

品名テーブル・・XHEAD
製品構成テーブル・・XPRTS 
所要テーブル・・XSACT

        Dim strSQL As String
        strSQL = "select XPRTS.CODE,H1.NAME,XPRTS.KCODE,H2.NAME,XSACT.SHOYOU,XSACT.KUBUN"
        strSQL = strSQL & " from XPRTS"

        strSQL = strSQL & " left join XHEAD H1 on XPRTS.CODE = H1.CODE"
        strSQL = strSQL & " left join XHEAD H2 on XPRTS.KCODE = H2.CODE"


条件文のCODEに対するKUBUNの『J』、『T』のみでさらにその中でCODEに対する最大値の抽出が
分かりません。

コードを教えて下さい。

追記コードです。

        Dim strSQL As String

        Dim fild1, fild2, fild3, fild4, fild5 As String
        Dim wTbl,wTbl5 As String

            fild1 = "CODE"
            fild2 = "NAME"
            fild3 = "KCODE"
            fild4 = "KUBUN"
            fild5 = "SHOYOU"

        strSQL = "select tmp.CODE,tmp.NAME,tmp.KCODE,tmp.KNAME,tmp.KUBUN,tmp.SYOYOU"

        strSQL = strSQL & " from (select " & wTbl & "." & fild1 & " CODE,H1." & fild2 & " NAME," & wTbl & "." & fild3 & " KCODE,H2." & fild2 & " KNAME," & wTbl5 & "." & fild4 & " KUBUN," & wTbl5 & "." & fild5 & " SHOYOU"
        strSQL = strSQL & ", MAX(" & wTbl5 & "." & fild5 & ") OVER(PARTITION BY " & wTbl & "." & fild1 & "," & wTbl5 & "." & fild4 & ") MAX_SHOYOU"
        strSQL = strSQL & " from " & wTbl

        strSQL = strSQL & " left join " & wTbl2 & " H1"
        strSQL = strSQL & " on " & wTbl & "." & fild1 & " = H1." & fild1

        strSQL = strSQL & " left join " & wTbl2 & " H2"
        strSQL = strSQL & " on " & wTbl & "." & fild3 & " = H2." & fild1

        strSQL = strSQL & " left join " & wTbl5
        strSQL = strSQL & " on " & wTbl & "." & fild1 & " = " & wTbl5 & "." & fild1 & " and " & wTbl5 & "." & fild4 & " in ('J','T')) tmp"

        strSQL = strSQL & " where tmp.SHOYOU = tmp.MAX_SHOYOU "
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+1

分析関数を使うのが良いでしょう。結果行そのものは集約せずに集約関数の結果を得ることが出来ます。SHOYOUの値が分析関数で求めたCODEとKCODEごとの最大値と等しい行を抽出すればよいです。

SELECT
    tmp.code
    ,tmp.name
    ,tmp.kcode
    ,tmp.kname
    ,tmp.shoyou
    ,tmp.kubun
FROM (
    SELECT
      k1.code code
      ,h1.name name
      ,k1.kcode kcode
      ,h2.name kname
      ,s1.shoyou shoyou
      ,s1.kubun kubun
      ,MAX(s1.shoyou) OVER(PARTITION BY k1.code, k1.kcode) max_shoyou
    FROM
      製品構成テーブル k1
      LEFT JOIN 品名テーブル h1 
        ON k1.code = h1.code
      LEFT JOIN 品名テーブル h2
        ON k1.kcode = h2.code
      LEFT JOIN 所要テーブル s1
        ON k1.code = s1.code
        AND s1.kubun IN ('J', 'T')
) tmp
WHERE tmp.shoyou = tmp.max_shoyou

'J' or 'T'は結合条件に指定します。

図でイメージするOracle DatabaseのSQL全集 第3回 分析関数

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/04/26 18:50

    回答ありがとうございます。
    コードは質問欄に追記します。
    フィールドとテーブルは変数にしてあります。

    ★実際のコードは5つのテーブルを結合し27のフィールドを条件抽出しています。
    5つのテーブルとフィールドを記載すると複雑になると思い、結合を2テーブルにして不明な条件の部分を質問しました。
    回答頂いた後、結合内容を変えて2つのテーブルを結合し質問させて頂いた条件抽出を行うようにコードを変えてみました。すると、上手く処理できました。
    残り3つのテーブルとフィールドで間違いがないか、もう一度チェックしてみます。
    質問に追記したコードは2つのテーブルを結合したコードです。(上手く処理できたコード)

    キャンセル

  • 2017/04/27 00:00

    追記いただいたコードは外側のSELECT句でSHOYOUがSYOYOUになっていますね。

    キャンセル

  • 2017/04/27 09:20

    回答ありがとうございます。御指摘ありがとうございます。
    コードのエラーは他のテーブルの特定フィールドが問題になっていました。
    おかげさまで問題をかなり限定でき、さらに分からなかった処理も分かりました。
    ありがとうございました。

    キャンセル

0

まず「条件文のCODEに対するKUBUNの『J』、『T』のみ」についてはWhere文で解決できると思います。
SQL文の最後に以下のような記述を足してみて下さい。

where XSACT.KUBUN = 'J' OR XSACT.KUBUN = 'T'

次の「最大値」というのはSQL文でやろうとするとGroup Byという構文を使うのですが、一発でやろうとすると今回作成したSQLをサブクエリにし、それをGroup By するというような使い方になります。
それを記述するためには「さらにその中でSHOYOUの最大値を抽出」とcd987456さんがおっしゃっている「さらにその中」ということの条件をもう少し詳しく伺う必要があります。

ここでは私の解釈として、「CODEとKCODEごとの最大値を抽出する」ということだとすると、恐らく以下のような形で求めたい結果を得ることは可能かと思います。

select CODE,KCODE,MAX(SHOYOU) As Max_SHOYOU
from
(select XPRTS.CODE,H1.NAME,XPRTS.KCODE,H2.NAME,XSACT.SHOYOU,XSACT.KUBUN
from XPRTS
left join XHEAD H1 on XPRTS.CODE = H1.CODE
left join XHEAD H2 on XPRTS.KCODE = H2.CODE
where XSACT.KUBUN = 'J' OR XSACT.KUBUN = 'T')
group by CODE , KCODE

ただし、GROUP BYを使ってしまうと出力できる項目がグループ化した項目と集計、最大項目等に限られます。例えばH1.NAMEなどはグループ化項目でも集計項目でもないので出力が出来ません。
上記についてはSQLの仕様ですのでGROUP BYについて調べてみて下さい。

上記より、個人的にはSQL文では最大値抽出までは行わず、一度「CODEに対するKUBUN は『J』か『T』のみを抽出」できた時点でExcelに出力し、そこから手作業でExcelにフィルタなどをかけ最大値を確認されるほうが業務的には扱い易いのではないかと想定します。
(上記は余計なお世話かもしれませんのでもしそうであれば聞き流して下さい。)

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/04/27 09:23

    回答ありがとうございます。
    GROUP BYを使う方法、今後の参考にさせて頂きます。
    ありがとうございました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.92%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る