SQLで最新のレコードを取得する3つの方法

20150420_01

こんにちは、やっと花粉症がおさまってきたシステムエンジニアのリョータです。
今年もすでに4ヶ月目に突入し、新たなスタートを切った方も多いのではないでしょうか。

今回は、最新のレコードを取得するSQLについて、解説を3つほどご紹介したいと思います。
下記のSQL文は、私がSQLを仕事で使い始めてからつまずいたもので、初心に戻ってまとめようと思ったのがきっかけです。
3つともサブクエリを使ったSQLとなっています。是非、ご参考にしてみてください。

条件

下のSQLは、全社員の所属部署(staff_section)テーブルの最新を取るものです。
社員番号(staff_no)が社員のキーになり、開始日(start_date)は必ず入力されています。
所属期間はかぶらないものとします。

社員番号(staff_no)でレコードが絞れる(※)なら、社員番号にインデックスを張ると、コストが改善されます。
一般的にはヒット率が全体の15%以下の場合。

SQLと解説

それぞれのSQLと解説をしていきます。
どのSQLにも一長一短なところがあるので、適宜使っていきましょう。

SQL1:最大の開始日と比較

SELECT
	*
FROM
	staff				sf01
LEFT OUTER JOIN
	staff_section		ss01
ON
	ss01.staff_no	=	sf01.no
WHERE
	ss01.start_date = (
		SELECT
			MAX(ss02.start_date)
		FROM
			staff_section	ss02
		WHERE
			ss02.staff_no	=	ss01.staff_no
		GROUP BY
			ss02.staff_no
	)

WHERE句の開始日の条件に「=」のサブクエリを使いました。
MySQLでは動きましたが、他ではエラーになるかもしれません。
そのときは、「=」を「IN」とするとうまくいくかもしれません。

クエリの考え方はシンプルです。

ss01テーブルで取得したレコードのうち、同じ社員番号の最大の開始日と等しいレコードを取得します。

欠点として、「=(IN)」で比較しているため、条件が複合化した場合に対応できません。

SQL2:最大の開始日のレコードが存在する

SELECT
	*
FROM
	staff				sf01
LEFT OUTER JOIN
	staff_section		ss01
ON
	ss01.staff_no	=	sf01.no
WHERE
	EXISTS (
		SELECT
			*
		FROM
			staff_section	ss02
		WHERE
			ss02.staff_no			=	ss01.staff_no
		GROUP BY
			ss02.staff_no
		HAVING
			MAX(ss02.start_date)	=	ss01.start_date
	)
WHERE句にEXISTSを使いました。

ss01テーブルで取得したレコードのうち、
同じ社員番号の最大の開始日と等しいレコードが存在する場合にレコードを取得します。

SQL1と比べて、条件が複合化してもHAVING句で対処できます。
ですが、このSQLでは必ず所属部署テーブルにレコードが存在する必要があります。

所属が決まっておらずレコードを登録しないレコード(まだ配属の決まっていない新入社員がいるなど)があると、取得できなくなります。

SQL3:自身の開始日よりも大きな開始日は存在しない

SELECT
	*
FROM
	staff				sf01
LEFT OUTER JOIN
	staff_section		ss01
ON
	ss01.staff_no	=	sf01.no
WHERE
	NOT EXISTS (
		SELECT
			*
		FROM
			staff_section	ss02
		WHERE
			ss02.staff_no	=	ss01.staff_no
		AND	ss02.start_date >	ss01.start_date
	)
WHERE句にNOT EXISTSのサブクエリを使いました。

ss01テーブルで取得したレコードの開始日より、開始日が大きいものがss02テーブルに存在しないレコードを取得しています。

このSQLの特徴は、所属部署テーブルに存在しない社員も表示されることです。
レコードがない可能性がある場合は、こちらを使いましょう。

MAX(start_date)から、NOT EXISTSへの変換は、
「開始日が一番大きい(最新)」から
「(自身の)開始日より大きい開始日は存在しない」
という読み替えによって行っています。

やってはいけないSQL

MySQLですと、このSQLもSQL3と同じ結果を返すことがあります。
SELECT
	*
FROM
	staff				sf01
LEFT OUTER JOIN
	(
		SELECT
			*
		FROM
			staff_section	ss02
		ORDER BY
			ss02.staff_no,
			ss02.start_date	DESC
	)					ss01
ON
	ss01.staff_no	=	sf01.no
GROUP BY
	sf01.no
「返すことがあります。」と書いたのは、私が実行した環境では同じように出力されましたが、別の環境ではわからないためです。

FROM句のサブクエリで開始日降順でソートし、社員番号でグループすると1番最初のレコードが取得されるらしく、同じ結果となるようです。

ただし、ソートには非常にコストがかかりますし、仕様も推測でしかなく、結果が異なる場合もあり得るので、使わないようにしましょう。

おわりに

MAXをMINに(>を<に)変更することで、最古のデータを取得することもできます。

MySQLでは、敬遠されていた歴史もあるサブクエリですが、十分に早くなりましたし、どんどん使っていきたいですね。

ではまた。

株式会社ライズウィル

株式会社ライズウィル

〒110-0016
東京都台東区台東1-27-11 やわらぎビル2F
TEL : 03-4590-3200
FAX : 03-4590-3201
E-Mail : info@risewill.co.jp
URL : https://www.risewill.co.jp

海上コンテナ輸送管理システム「海コン魂!」
海コン魂!
面接予約・スタッフ管理システム「WebWORKS.」
WebWORKS.