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

Question

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

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

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

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

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

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

【所要テーブル】
フィールドは２つで”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 "

Answer 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回 分析関数

Comments

[cd987456]

回答ありがとうございます。
上記コードで教えて頂きたいことがあります。
・最初のselectの後～fromまでは「フィールド」だけの指定ですか？
・2番目のselectの後～maxまでは「テーブル.フィールド　別名」ですか？
・max_shoyouは　max_　の後に直接フィールド指定ですか？
細かい事をすいません。教えて下さい。

[SVC34]

・最初のselectの後～fromまでは「フィールド」だけの指定ですか？
⇒その通りです（サブクエリでつけた別名を指定しています）
・2番目のselectの後～maxまでは「テーブル.フィールド　別名」ですか？
⇒その通りです
・max_shoyouは　max_　の後に直接フィールド指定ですか？
⇒いいえ、"max_shoyou"全体が関数 "MAX(s1.shoyou) OVER(PARTITION BY k1.code, k1.kcode)" で生成されたカラムにつけた別名です。

[cd987456]

早速の回答ありがとうございます。
条件指定の　WHERE shoyou = max_shoyou　の所だと思うのですが、
WHERE shoyou　とすると「ORA-00918: 列の定義が未確定です。」と出てしまいます。
WHERE　s1.shoyou　とすると「ORA-00904: 無効な識別子です」と出てしまいます。
列指定に問題はないように思うのですが、もし、思い当たる箇所があれば、教えて下さい。

[SVC34]

サブクエリに別名をつけて、外側のクエリの識別子で明示指定するよう修正しました。これでどうでしょう。

[cd987456]

回答ありがとうございます。
また、「ORA-00918: 列の定義が未確定です。」と出てしまいました。
２番目のselectの後、
「s1.shoyou shoyou」と
「MAX(s1.shoyou) OVER(PARTITION BY k1.code, k1.kcode) max_shoyou」
で列が重なる事はありますか？
何度もすいませんが、教えて下さい。

[SVC34]

列名には修飾を全て明示指定しているので、ORA-00918が出るのは変ですね。shoyouとmax_shoyouがぶつかることもありえません。実機でも文法エラーとならないことを確認しています。そちらのSQLを質問に追記していただけますか。

[cd987456]

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

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

[SVC34]

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

[cd987456]

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

Answer 2

まず「条件文の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にフィルタなどをかけ最大値を確認されるほうが業務的には扱い易いのではないかと想定します。
（上記は余計なお世話かもしれませんのでもしそうであれば聞き流して下さい。）

Comments

[cd987456]

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