相関サブクエリを使って、各キーごとの最大値を取得する

相関サブクエリとは

SQLのサブクエリ内で、サブクエリ外部のテーブルを参照するサブクエリのこと。
以下の例では、相関サブクエリで、
companyテーブルのareaと同じ値を持つdeptテーブルのレコードを抽出している。

SELECT
  dept.person
FROM
  dept
WHERE
dept.area IN (
  SELECT
    compay.area
  FROM
    compay
)

複数条件に合致する最大値の取得

銀行テーブルから、取引日が2016/01/25の過去直近の各口座の残高を抽出する例を記載する。

• 銀行テーブル

acc_no	trans_date	balance
1	2016/01/25	30000
1	2016/01/24	20000
2	2016/01/23	5000
2	2016/01/22	4500

SELECT
  ACC1.balance
FROM
  ACCOUNT AS ACC1
WHERE
  ACC1.tran_date = (SELECT MAX(tran_date)
                   FROM　  ACCOUNT AS ACC2
                   WHERE   ACC1.acc_no = ACC2.acc_no
                   AND     ACC2.trans_date <= '2016-01-25 00:00:00')

上記SQLを実行すると、以下の情報が取得できる。

acc_no	trans_date	balance
1	2016/01/25	30000
2	2016/01/23	5000

• 仕組み
  相関サブクエリで、ACC1.acc_no = ACC2.acc_noとacc_noで
  サブクエリの外側のテーブルと紐付けている。
  これによって、各口座番号ごとに、日付が最大かどうかを判断する
  線引きをしている。

SELECT '1', '2016-01-25',   30000 FROM ACCOUNT WHERE '2016-01-25' = '2016-01-25';
SELECT '1', '2016-01-24',   20000 FROM ACCOUNT WHERE '2016-01-24' = '2016-01-25';
―――――――――――――――――――――――――――――――――――― ←口座番号を分ける境界線
SELECT '2', '2016-01-23',   5000   FROM ACCOUNT WHERE '2016-01-23' = '2016-01-23';
SELECT '2', '2016-01-22',   4500   FROM ACCOUNT WHERE '2016-01-22' = '2016-01-22';

以下の記事が参考になりました。

gihyo.jp