@drinkingdrinkingposted at 2021-06-17

【SQL, DB2】LEFT JOIN (サブクエリ) ON で1件のみ結合したい

Q&A

Closed

要約

現在DB2で複数テーブルからデータを取得するためのSQLを作成していて、わからない部分があるので教えていただきたいです。
端的に言うと、SELECT文のLEFT JOIN句でサブクエリを結合しようとしていますが、複数レコードに分かれてしまう場合、結合条件で絞り込んだ後にサブクエリの1行目のみ結合したいという内容です。

データ取得元テーブル

以下のような帳簿テーブルと、帳簿に紐づく明細テーブル、項目を追加した帳簿写しテーブルが存在するとします。

解決したいこと

1 帳簿から集計したエリアごとの数量・金額の集計表

に、

2 受注フラグが「1」の帳簿写しの商品の販売形態として区分昇順, 帳簿No降順で並べた表

を結合したいです。

-- 1. エリアごとの集計表
SELECT 
帳簿.日付
, 帳簿.エリア
, 明細.商品
, SUM(明細.数量)
, SUM(明細.金額)
FROM
帳簿 
LEFT JOIN 明細
ON 帳簿.帳簿No = 明細.帳簿No
WHERE
帳簿.日付 = '6月1日'
AND 帳簿.エリア = 東京
GROUP BY
帳簿.日付
, 帳簿.エリア
, 明細.商品

-- 2. 受注フラグ=1の帳簿写しの販売形態表
SELECT
帳簿写し.日付
, 帳簿写し.エリア
, 明細.商品
, 明細.販売形態 AS 販売形態
FROM
帳簿写し
LEFT JOIN 明細
ON 帳簿写し.帳簿No = 明細.帳簿No
WHERE
帳簿写し.受注フラグ = '1'
ORDER BY
帳簿写し.区分 ASC
, 帳簿写し.帳簿No DESC

1と2をそれぞれ実行すると、以下の表のようになると思います。

1に2を左外部結合したいわけですが、このままだと、同じ商品のレコードが複数作成されてしまいます。2の「販売形態」はあくまで一例として該当の商品の最初の1レコードのみ結合したいです。

期待する結果は、以下です。

しかし、FETCH FIRST 1 ROWS ONLY などはサブクエリ内では使えても、結合条件では使えないようで、どのように書けば期待する結果となるかわからないでいます。

--- ダメな例
帳簿.日付
, 帳簿.エリア
, 明細.商品
, SUM(明細.数量)
, SUM(明細.金額)
FROM
帳簿 
LEFT JOIN 明細
ON 帳簿.帳簿No = 明細.帳簿No
LEFT JOIN(
　SELECT
　帳簿写し.日付
　, 帳簿写し.エリア
　, 明細.商品
　, 明細.販売形態 AS 販売形態
　FROM
　帳簿写し
　LEFT JOIN 明細
　ON 帳簿写し.帳簿No = 明細.帳簿No
　WHERE
　帳簿写し.受注フラグ = '1'
　ORDER BY
　帳簿写し.区分 ASC
　, 帳簿写し.帳簿No DESC
---ここにFETCH FIRST 1 ROWS ONLY 入れたらりんごの明細しかとれない
)Z
ON Z.日付 = 帳簿.日付
AND Z.エリア = 帳簿.エリア
AND Z.商品 = 明細.商品
--- ここにFETCH FIRST 1 ROWS ONLY入れたらエラーになる
WHERE
帳簿.日付 = '6月1日'
AND 帳簿.エリア = 東京
GROUP BY
帳簿.日付
, 帳簿.エリア

なにか良い方法はありますでしょうか。
教えてくださるとありがたいです。

1 likes

1Answer

@YottyPG posted at 2021-06-17

2の「販売形態」はあくまで一例として該当の商品の最初の1レコードのみ結合したいです。

どのレコードでもよければサブクエリをGROUP BYして販売形態のMAXをとってきてあげるだけ実装できるかと思います。

SELECT
  #帳簿写し.日付
, #帳簿写し.エリア
, #明細.商品
, MAX(#明細.販売形態) AS 販売形態
FROM
#帳簿写し
LEFT JOIN #明細
ON #帳簿写し.帳簿No = #明細.帳簿No
WHERE
#帳簿写し.受注フラグ = '1'
GROUP BY
  #帳簿写し.日付
, #帳簿写し.エリア
, #明細.商品

該当の商品の最初の1レコードこの要件が重要なのであれば、ROW_NUMBERを使えば実装できると思います。
「日付、エリア、商品」ごとに「帳簿No、明細No」の若い順に番号を振ってあげます。

SELECT
  ROW_NUMBER() OVER(PARTITION BY #帳簿写し.日付,#帳簿写し.エリア,#明細.商品  ORDER BY #帳簿写し.帳簿No, #明細.明細No) AS RowNo
, #帳簿写し.日付
, #帳簿写し.エリア
, #明細.商品
 ,#明細.販売形態
FROM
#帳簿写し
LEFT JOIN #明細
ON #帳簿写し.帳簿No = #明細.帳簿No
WHERE
#帳簿写し.受注フラグ = '1'

このクエリで以下のような表が取れてこれるので、RowNoが1のところだけを結合してあげればうまくいくと思います。

SELECT
  #帳簿.日付
, #帳簿.エリア
, #明細.商品
, SUM(#明細.数量)
, SUM(#明細.金額)
, MAX(z.販売形態)
FROM
#帳簿 

LEFT JOIN #明細
ON #帳簿.帳簿No = #明細.帳簿No

LEFT JOIN(
    SELECT
      ROW_NUMBER() OVER(PARTITION BY #帳簿写し.日付,#帳簿写し.エリア,#明細.商品  ORDER BY #帳簿写し.帳簿No, #明細.明細No) AS RowNo
    , #帳簿写し.日付
    , #帳簿写し.エリア
    , #明細.商品
     ,#明細.販売形態
    FROM
    #帳簿写し
    LEFT JOIN #明細
    ON #帳簿写し.帳簿No = #明細.帳簿No
    WHERE
    #帳簿写し.受注フラグ = '1'
)AS Z
 ON Z.日付 = #帳簿.日付
AND Z.エリア = #帳簿.エリア
AND Z.商品 = #明細.商品
AND Z.RowNo = 1

WHERE
    #帳簿.日付 = '2021/6/1'
AND #帳簿.エリア = '東京'

GROUP BY
  #帳簿.日付
, #帳簿.エリア
, #明細.商品

1Like

Comments

@drinkingdrinking
Questioner
@YottyPGさん
ご回答ありがとうございます。

＞「該当の商品の最初の1レコード」この要件が重要なのであれば、ROW_NUMBERを使えば実装できると思います。

ROW_NUMBERを使って問題なく実装できました！
PARTITION BYでグループ分けした上で採番可能なんですね。
大変勉強になりました。
ありがとうございました。

Are you sure you want to delete the question?