テーブル結合について (Part.3) －内部結合を極めよう

データベース

技術解説

連載 [第7回] :

初心者のための SQL-BOOTCAMP

2023年4月25日(火)

久保司

第7回となる今回は、実践的な問題を解きながら、利用頻度の高い内部結合について深掘りしていきます。

(2)受注商品の集計

2問目は、集計関数、GROUP BY句を使用した内部結合です。

【問題】
2021年11月5日に受注した商品の受注数量と受注金額を商品毎に集計しなさい。受注金額は、単価(UNITPRICE)と受注数量(QUANTITY)を掛けて求めること。

表示項目は以下とする(エイリアスを使用し→の項目名とする)。

ITEM_CODE → I_CD
ITEM_NAME → I_NM
QUANTITY → OD_QT
(UNITPRICE * QUANTITY) → OD_AMT

表示順

受注金額の降順
商品コードの昇順

【使用するテーブルのER図(半角:物理名/全角:論理名)】

【準備】
商品マスタのデータは1問目で挿入したデータを使用します。受注テーブルと受注明細テーブルにデータを追加で挿入します。

【受注テーブルINSERTコマンド】

INSERT INTO ORDER_TBL
 (ORDER_NO,ORDER_DATE,WH_CODE,ORDER_AMNT) VALUES
 ('OR410','2021-11-04','100','495')
, ('OR500','2021-11-05','100','1640')
, ('OR501','2021-11-05','200','2680')
, ('OR502','2021-11-05','200','720')
, ('OR503','2021-11-05','300','1780')
, ('OR504','2021-11-05','200','3160')
, ('OR600','2021-11-05','100','3240')
, ('OR510','2021-11-05','300','3180')
, ('OR530','2021-11-05','300','888');

【受注明細テーブルINSERTコマンド】

INSERT INTO ORDER_DETAILS
 (ORDER_NO,OD_ROW_NO,ITEM_CODE,UNITPRICE,QUANTITY) VALUES
 ('OR410','1','150','99','5'), ('OR500','1','100','100','2')
, ('OR500','2','110','120','4'), ('OR500','3','120','140','3')
, ('OR500','4','130','540','1'), ('OR501','1','110','120','3')
, ('OR501','2','120','140','5'), ('OR501','3','130','540','3')
, ('OR502','1','110','120','6'), ('OR503','1','120','140','5')
, ('OR503','2','130','540','2'), ('OR504','1','100','100','1')
, ('OR504','2','110','120','5'), ('OR504','3','120','140','6')
, ('OR504','4','130','540','3'), ('OR600','1','160','810','4')
, ('OR510','1','100','100','7'), ('OR510','2','110','120','9')
, ('OR510','3','120','140','10'), ('OR530','1','190','888','1');

【解答例】

SELECT
      T2.ITEM_CODE                      AS I_CD
    , T3.ITEM_NAME                      AS I_NM
    , SUM(T2.QUANTITY)                  AS OD_QT  -- ④
    , SUM(T2.UNITPRICE * T2.QUANTITY)   AS OD_AMT -- ⑤
FROM
    ORDER_TBL AS T1 -- ①
    INNER JOIN ORDER_DETAILS AS T2 -- ②
        ON T1.ORDER_NO = T2.ORDER_NO 
    INNER JOIN ITEM_MST AS T3 -- ③
        ON T2.ITEM_CODE = T3.ITEM_CODE 
WHERE -- ⑥
    T1.ORDER_DATE = '2021-11-05' 
GROUP BY -- ⑦
    T2.ITEM_CODE
    , T3.ITEM_NAME 
ORDER BY -- ⑧
    OD_AMT DESC
    , T2.ITEM_CODE ASC;

【説明】
　① 受注日を絞り込むために、受注テーブルをメインテーブルとしています
　② 受注数量を取得するために、受注明細テーブルを受注番号で内部結合しています
　③ 商品名を取得するために、商品マスタを受注明細データの商品コードで内部結合しています
　④ SUM関数を使用して、商品コード、商品名毎に受注数量を集計しています
　⑤ SUM関数を使用して、商品コード、商品名毎に単価と数量を掛けた値を集計しています
　⑥ 受注日が2021年11月５日の受注データを絞り込んでいます
　⑦ 商品コードと商品名をGROUP BY句でグルーピングしています(表示項目に商品名が含まれているため、ここでも商品名を指定。表示項目に商品名が指定されていない場合、商品名は不要) 　⑧ ;単価と数量を掛ける計算式をAS句で変換した「OD_AMT」をそのままソート項目に使用しています

【結果】
受注日が2021年11月5日のデータが受注金額の降順、受注金額が同じ場合は商品コードの昇順で表示されていることが確認できます。

ここでは、利用頻度の高いデータ集計と内部結合の組み合わせの例を紹介しました。これまでに紹介してきたSQL文に比べ、より実践的なSQL文となりますが、もう皆さんにとっては簡単だったかも知れません(※集計関数、GROUP BY句の詳細については第4回を参照してください。

おわりに

今回は、実践形式で内部結合を使用した問題を解きながら解答のSQL文を解説しました。SQL文を習得するには避けては通れない重要な「テーブル結合」を3回に渡って説明してきましたが、皆さんも色々な「テーブル結合」を試して、ぜひ強力な武器を身に付けてください。

最後に、SQLの腕試しができるオンラインコンテスト「TOPSIC SQL CONTEST (TSC)」を紹介します。簡単な会員登録をするだけで、だれでも無料でコンテストに参加できるのでお手軽です。コンテストは不定期に開催されますが、過去のコンテスト問題も解答できるので、ぜひ、チャレンジしてみてください。

上記「TOPSIC SQL CONTEST (TSC)」サイト内にある、下記のアイコンから会員登録ができます。