今回は、最新のレコードを取得するSQLについて、3つの方法をご紹介したいと思います。
私がSQLを仕事で使い始めてから、よくつまずくものなので、初心に戻ってまとめようと思います。
3つともサブクエリを使ったSQLとなっています。是非、ご参考にしてみてください。
条件
今回のSQLは、社員の所属部署(section)テーブルの最新レコードを取るものとなります。
社員番号(staff_no)がキーになり、開始日(start_date)は必ず入力されています。
また所属期間はかぶらないものとします。
最新のレコードを取得する3つの方法
それぞれのSQLと解説をしていきます。
どのSQLにも一長一短なところがあるので、適宜使っていきましょう。
1.最大の開始日と比較
SELECT * FROM staff LEFT OUTER JOIN section AS s1
ON staff.staff_no = s1.staff_no
WHERE
s1.start_date = (
SELECT MAX(s2.start_date) FROM section AS s2
WHERE
s2.staff_no = s1.staff_no
GROUP BY
s2.staff_no
)
WHERE句の開始日の条件に「=」のサブクエリを使いました。
考え方はシンプルです。
(日付の最大) イコール (最も新しい日付) となることから、 sectionテーブルの中で、同じ社員番号の最大の開始日と等しいレコードを取得します。
欠点として、「=」で比較しているため、条件が複合化した場合に対応できません。
2.最大の開始日のレコードが存在する
SELECT * FROM staff LEFT OUTER JOIN section AS s1
ON staff.staff_no = s1.staff_no
WHERE
EXISTS (
SELECT * FROM section AS s2
WHERE
s2.staff_no = s1.staff_no
GROUP BY
s2.staff_no
HAVING
MAX(s2.start_date) = ss01.start_date
)
WHERE句にEXISTSを使いました。
s1テーブルで取得したレコードのうち、同じ社員番号の最大の開始日と等しいレコードが存在する場合にレコードを取得します。
一つ目のSQLと比べて、条件が複合化してもHAVING句で対処できます。
ですが、このSQLでは必ず所属部署テーブルにレコードが存在する必要があります。
所属が決まっておらずレコードを登録しないレコード(まだ配属の決まっていない新入社員がいるなど)があると、取得できなくなります。
3.自身の開始日よりも大きな開始日は存在しない
SELECT * FROM staff LEFT OUTER JOIN section AS s1
ON staff.staff_no = s1.staff_no
WHERE
NOT EXISTS (
SELECT * FROM section AS s2
WHERE
s2.staff_no = s1.staff_no
AND s2.start_date > s1.start_date
)
WHERE句にNOT EXISTSのサブクエリを使いました。
s1テーブルで取得したレコードの開始日より、開始日が大きいものがs2テーブルに存在しないレコードを取得しています。
このSQLの特徴は、所属部署テーブルに存在しない社員も表示されることです。
レコードがない可能性がある場合は、こちらを使いましょう。
MAX(start_date)から、NOT EXISTSへの変換は、
「開始日が一番大きい(最新)」から
「(自身の)開始日より大きい開始日は存在しない」
という読み替えによって行っています。
おわりに
MAXをMINに変更することで、最古のデータを取得することもできます。
最新のレコードを取得する場面は、何かとあると思いますので考え方だけでも覚えてくださいね。
コメント