たぶんよく使うSQLです。
あるデータを履歴管理する場合のデータの抽出方法です。以下のようなエンティティを作るとします。
国識別テーブル
| 識別番号 | 国名 | 属性 |
| A | A共和国 | ライトサイド |
| B | B帝国 | ダークサイド |
| C | C自治区 | ライトサイド |
戦況履歴テーブル
| 履歴番号 | 識別番号 | 状況 | 人数 |
| 1 | A | 突撃 | 1000 |
| 2 | A | 進行 | 900 |
| 3 | A | 退却 | 300 |
| 4 | B | 突撃 | 900 |
| 5 | A | 突撃 | 500 |
| 6 | B | 退却 | 800 |
| 7 | C | 進行 | 710 |
| 8 | B | 進行 | 850 |
このとき、「属性がライトサイドの『国名』と最新の『状況』とその時の『人数』」を確認したい場合、以下のようなSQLになります。
SELECT
X.国名,
Y.状況,
Y.人数
FROM
国識別テーブル X
INNER JOIN 戦況履歴テーブル Y ON
Y.識別番号 = X.識別番号
AND Y.履歴番号 = ( SELECT MAX(Z.履歴番号) FROM 戦況履歴テーブル Z WHERE Z.識別番号 = Y.識別番号 )
WHERE
X.属性 = 'ライトサイド'
ここでは『最新』を「戦況履歴テーブルの履歴番号の最大」と定義しています。
他のにも「戦況履歴テーブルの履歴番号降順の1件目」という定義の仕方があります。その場合は、
例えばPostgreSQLやMySQLなどでは
AND Y.履歴番号 = ( SELECT Z.履歴番号 FROM 戦況履歴テーブル Z WHERE Z.識別番号 = Y.識別番号 ORDER BY Z.履歴番号 DESC LIMIT 1)
Oracleでは
AND Y.履歴番号 = ( SELECT ZZ.履歴番号 FROM (SELECT Z.履歴番号 FROM 戦況履歴テーブル Z WHERE Z.識別番号 = Y.識別番号 ORDER BY Z.履歴番号 DESC) ZZ WHERE ROWNUM = 1)
と表現することもできます。どの表現にするかは、業務の場合は性能要件や保守要件等を考慮して設計するようにしましょう。(インデックスの設定や永年同じDBを使い続けるか等)
結果
| 国名 | 状況 | 人数 |
| A共和国 | 突撃 | 500 |
| C自治区 | 進行中 | 710 |
ここでのポイント：
戦況履歴テーブルの「識別番号」毎に最大の「履歴番号」を取得するようにします。
次に、「最後に退却した『国名』と『人数』と『履歴番号』」を確認したい場合はどうでしょう？以下のようなSQLになります。
SELECT
X.国名,
Y.人数,
Y.履歴番号
FROM
国識別テーブル X
INNER JOIN 戦況履歴テーブル Y ON
Y.識別番号 = X.識別番号
AND Y.履歴番号 = ( SELECT MAX(Z.履歴番号) FROM 戦況履歴テーブル Z WHERE Z.状況 = '退却' )
結果
| 国名 | 人数 | 履歴番号 |
| B帝国 | 800 | 6 |
ここでのポイント：
戦況履歴テーブルのみを条件にしているので、先ほどとは違い「識別番号」を条件に含まないようにします。
では、「900人未満で最初に進行した『国名』と『人数』」はどのようなSQLで確認できるでしょうか？
