テーブルの最新年月日の情報を取得するSQLを考えたいと思います。
カラムが2つしかない場合の最新日付のレコードの抽出
以下のようなPRODUCT、STARTDATEという2つのカラムを持つテーブルを用意します。
やりたいことは、PRODUCTごとの最新年月日(STARTDATEが最大)を取得することです。PRODUCTごとに最大のSTARTDATEは、以下のSQLで抽出できます。
select product,max(startdate) from test_history1 group by product;
＜参考：test_history1テーブルの作成＞
product varchar(10)
, startdate date
);
insert into test_history1 (product,startdate) values ('apple','202/01/05');
insert into test_history1 (product,startdate) values ('apple','202/01/10');
insert into test_history1 (product,startdate) values ('grape','202/01/02');
insert into test_history1 (product,startdate) values ('grape','202/01/6');
insert into test_history1 (product,startdate) values ('grape','202/01/11');
カラムが3つ以上ある場合の最新日付のレコードの抽出
以下のようなCLASSIFICATION(分類コード)、PRODUCT(商品コード)、PRICE、STARTDATEというカラムがあるテーブルで、最新日付のレコードを抽出します。
※分類コード、商品コード、価格、価格の適用日を保管している履歴テーブルとします。
例えば、CLASSIFICATION、PRODUCTの組み合わせに対して、最新のSTARTDATEのレコードを抽出するSQLは、以下の通りです。
a.classification
, a.product
, a.startdate
, a.price
from
test_history a
inner join (
select
classification
, product
, max(startdate) maxdate
from
test_history
group by
classification
, product
) b
ON a.classification = b.classification
and a.product = b.product
and a.startdate = b.maxdate;
※分類：Aの商品コード：1のSTARTDATE：2020/05/11が2件あるので、上記のような結果になっいます。同一の日をはじくようにテーブルの制約をかけないとですね。
INNER JOINを使って内部結合をして、抽出します。
考え方は以下の通りです。
SELECT 取得したい列
FROM テーブル 別名A
INNER JOIN (SELECT グループ列, MAX(最大値を抽出する列) 別名 FROM テーブル GROUP BY グループ列) 別名 B
ON 別名A.グループ列 ＝ 別名B.グループ列
AND 別名A.最大値を求める列 = 別名B.最大値;
まず、以下のようにCLASSIFICATION、PRODUCTの組み合わせで、最新日付を出すSQLを考えます。
select
classification
, product
, MAX(startdate)
from
test_history
group by
classification
, product ;
この情報に対して、PRICEを付けるために、内部結合してデータを抽出します。
＜参考：test_historyテーブルの作成＞
create table test_history(
classification VARCHAR2(5)
, product NUMBER(2)
, price NUMBER(10)
, startdate date
);
データはA5SQL-mk2でExcelの情報をコピー＆ペーストして挿入しました。
テーブルが不要になれば、DROP TABLE FROM テーブル名;で削除できます。
