クエリ グループで最大値をもつレコードの抽出（相関サブクエリ）

　以前の記事で、テーブル全体から最大値をもつレコードを抽出する方法をとりあげましたが、今回はグループ毎の最大値のレコードを抽出します。

　データはこんな感じ。
　担当Aさん、Bさん、Cさん･･･が居て、日付毎に、金額が一番大きかった人のレコードを抽出します。

　まず、以前と同じ考え方でSQLを作成します。
　日付毎に最大の金額を求めるSQLを作成します。

SELECT 日付, Max(T実績.金額) AS 金額
FROM T実績
GROUP BY 日付

　結果は、以下の通り。

　日付と金額が得られたので、これと一致するレコードを抽出するSQLを書けばできあがりです。
　しかし、前回のSQLの、
SELECT 日付, 金額
FROM T売上
WHERE 金額 = ( SELECT MAX( 金額 ) FROM T売上 )
　という方法は、サブクエリの結果が1件のときしか利用できません。今回は、条件にしたい項目も2つあります。
　なので、今回は、WHERE条件ではなく、テーブル結合で抽出します。

SELECT A.日付,A.担当,A.金額
FROM T実績 AS A INNER JOIN
(SELECT T実績.日付, Max(T実績.金額) AS 金額
FROM T実績
GROUP BY T実績.日付) AS B
ON (A.金額 = B.金額) AND (A.日付 = B.日付)

　一致のみで良いので、INNER JOINで結合します。

　最終結果。

　該当レコードが抽出できました。

○相関サブクエリの例

　さて、上記の方法でも目的の結果が得られますが、別のやり方もあります。

SELECT A.日付,A.担当,A.金額
FROM T実績 AS A
WHERE A.金額=
( SELECT MAX(B.金額)
FROM T実績 AS B
WHERE B.日付 = A.日付 )

　こういう書き方です。

　親のクエリのWHERE条件に、サブクエリを使っているところは、前回ケースのSQLに似ています。
　しかし、サブクエリに、「WHERE A.日付 = B.日付」という、条件があります。
　このようなサブクエリは、相関サブクエリと言うらしいです。

　このSQLがどのように処理されるか、順を追って説明します。
　まず最初に、親クエリのテーブルから、1件レコードが取り出され、評価されます。
　テーブルの1件目、「2012/01/01・A・990」のレコードが取り出されたとします。

　このレコード内容に対して、サブクエリが実行されます。
　具体的には、サブクエリの「A.日付」に、レコードの値が設定された、
SELECT MAX(B.金額)
FROM T実績 AS B
WHERE B.日付 = #2012/01/01#
　が、実行されます。
　2012/01/01の最大金額は、1900ですので、この値がサブクエリの結果になります。

　この結果を元に、親クエリのWHERE条件
WHERE A.金額=
( SELECT MAX(B.金額)
FROM T実績 AS B
WHERE B.日付 = A.日付 )
　は、
WHERE 990=1900
　という内容になります。
　この条件は成立していませんので、1件目のレコードは結果として抽出されません。

　これと同じ事を3件目のデータ、「2012/01/01・C・1900」でやると、
WHERE 1900=1900
　となり、成立するので、このレコードは結果として抽出されます。

　つまり、相関サブクエリは、親クエリのレコード1件に対して、サブクエリが1回実行されます。

　全レコードの処理イメージを表にすると、以下のような感じです。

　イコールが成り立ったレコードが、結果に抽出される、といった具合です。

　相関サブクエリは、親クエリから1件ずつ処理される、と言う事を念頭に置いて、レコードのデータを当てはめながら処理を追ってみると、分かり易いと思います。