【Oracle SQL】外部結合でキーごとに1件のデータを抽出する方法！

スポンサードリンク

どうも、しぐま（@sigma__blog ）です！

この間、仕事でSQLを作成している中で苦戦したものがありました。

内容はタイトルの通りなのですが、詳細を言葉だけで説明するのが難しいので、テーブルのイメージも交えて説明します！

今回は下記の2テーブルを例に説明をします。

顧客テーブル(T_CUSTOMER)

CUST_ID	CUST_NAME	AGE
0001	山田　太郎	20
0002	山田　花子	24
0003	山田　香奈	22

購入品テーブル(T_PURCHASED_ITEM)

CUST_ID	PURCH_ITEM	PRICE	PURCH_DATE
0003	バッグ	7000	2019/04/02
0001	カメラ	108000	2019/06/20
0002	ペン	240	2019/02/04
0001	シャツ	3000	2019/04/01
0002	時計	12000	2019/03/17

この2テーブルを普通に外部結合すると、

CUST_ID	CUST_NAME	PURCH_ITEM	PRICE
0003	山田　香奈	バッグ	7000
0001	山田　太郎	カメラ	108000
0002	山田　花子	ペン	240
0001	山田　太郎	シャツ	3000
0002	山田　花子	時計	12000

こうなりますよね。

書くまでもないですが一応、結合SQLはこちら。

SELECT A.CUST_ID,A.CUST_NAME,B.PURCH_NAME,B.PRICE FROM T_CUSTOMER A
LEFT OUTER JOIN T_PURCHASED_ITEM B
ON A.CUST_ID = B.CUST_ID;

SELECT A.CUST_ID,A.CUST_NAME,B.PURCH_NAME,B.PRICE FROM T_CUSTOMER A

LEFT OUTER JOIN T_PURCHASED_ITEM B

ON A.CUST_ID = B.CUST_ID;

それで、例えば顧客ごとに購入金額が最大のレコードのみを抽出したい、という場合はどのようにすればよいでしょうか。

結果イメージはこんな感じ。

CUST_ID	CUST_NAME	PURCH_ITEM	PRICE
0001	山田　太郎	カメラ	108000
0002	山田　花子	時計	12000
0003	山田　香奈	バッグ	7000

僕が最初に試したのが次のSQLです。

SELECT A.CUST_ID,A.CUST_NAME,B.PURCH_ITEM,B.PRICE FROM T_CUSTOMER A 
LEFT OUTER JOIN T_PURCHASED_ITEM B 
ON A.CUST_ID = B.CUST_ID AND ROWNUM = 1;

SELECT A.CUST_ID,A.CUST_NAME,B.PURCH_ITEM,B.PRICE FROM T_CUSTOMER A

LEFT OUTER JOIN T_PURCHASED_ITEM B

ON A.CUST_ID = B.CUST_ID AND ROWNUM = 1;

結果はというと、「AND ROWNUM = 1」を付ける前のSQLと同じ結果が得られました。

どうやらROWNUMの指定は結合条件には意味が無いようです。

というか、これで仮にデータを1件抽出できたとしても、きっと「購入金額が最大のレコード」では無いですね(笑)

それではどうすればよいか。

まずは結果を先に載せておきます。

SELECT A.CUST_ID,A.CUST_NAME,B.PURCH_ITEM,B.PRICE FROM T_CUSTOMER A
LEFT OUTER JOIN T_PURCHASED_ITEM B
ON A.CUST_ID = B.CUST_ID
AND NOT EXISTS (SELECT 1 FROM T_PURCHASED_ITEM C WHERE C.CUST_ID = A.CUST_ID AND C.PRICE > B.PRICE);