【SQL】グループ中の最大値のレコードを抽出する方法
2019/03/01
SQLのGROUP BY句とMAX()を利用して、グループ化したレコード中から最大値をもつレコードを抽出する方法を備忘録としてポストします。
以下の様な社員マスタ、出張先マスタ、出張管理テーブルがあるとします。
出張管理テーブルから、各社員の最新の出張先を抽出したい場合のSQLを考えます。
GROUP BY 及び MAX() でグループ中の最大値を抽出する
レコード作成
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
-- -- 社員マスタ -- CREATE TABLE `users`( `id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`) ) TYPE = InnoDB DEFAULT CHARSET=utf8; INSERT INTO `users` VALUES(null, '鈴木'); INSERT INTO `users` VALUES(null, '渡嘉敷'); INSERT INTO `users` VALUES(null, '夏目'); INSERT INTO `users` VALUES(null, '野々村'); INSERT INTO `users` VALUES(null, '泉'); INSERT INTO `users` VALUES(null, '虹野'); -- -- 出張先マスタ -- CREATE TABLE `places`( `id` INT UNSIGNED AUTO_INCREMENT, `place` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`) ) TYPE = InnoDB DEFAULT CHARSET=utf8; INSERT INTO `places` VALUES(null, '大阪'); INSERT INTO `places` VALUES(null, '東京'); INSERT INTO `places` VALUES(null, '福岡'); INSERT INTO `places` VALUES(null, '札幌'); INSERT INTO `places` VALUES(null, '沖縄'); INSERT INTO `places` VALUES(null, '石川'); -- -- 出張管理テーブル -- CREATE TABLE `trips`( `id` INT UNSIGNED AUTO_INCREMENT, `name_id` INT NOT NULL, `place_id` INT NOT NULL, `date` DATE default NULL, PRIMARY KEY (`id`) ) TYPE = InnoDB DEFAULT CHARSET=utf8; INSERT INTO `trips` VALUES(null, 1, 5, '2015-6-15'); INSERT INTO `trips` VALUES(null, 2, 2, '2015-4-12'); INSERT INTO `trips` VALUES(null, 6, 3, '2015-9-10'); INSERT INTO `trips` VALUES(null, 4, 4, '2015-2-24'); INSERT INTO `trips` VALUES(null, 5, 1, '2015-8-23'); INSERT INTO `trips` VALUES(null, 3, 3, '2015-6-14'); INSERT INTO `trips` VALUES(null, 2, 5, '2015-9-22'); INSERT INTO `trips` VALUES(null, 5, 4, '2015-8-31'); INSERT INTO `trips` VALUES(null, 1, 6, '2015-1-19'); INSERT INTO `trips` VALUES(null, 3, 6, '2015-10-24'); INSERT INTO `trips` VALUES(null, 4, 2, '2015-5-27'); INSERT INTO `trips` VALUES(null, 6, 1, '2015-6-10'); |
社員マスタ (users)
出張先マスタ (places)
出張管理テーブル (trips)
SQLを考える
考え方としては、「社員ごと」でレコードを「グループ化」し日付の大きいレコードを抽出すれば良い、ということ。鈴木(user_id:1)で考えると、
|
1 |
SELECT * FROM `trips` WHERE user_id = 1; |
の内、id:1のレコードが対象となるようです。
tripsテーブルの内部IDが大きい方が最新と思いがちですが、そうでない場合も多々考えられるので、IDを比較するのではなく、日付を比較するのが吉。
これをSQLに落としこむと、以下のようになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT trip.id, user.name, place.place, trip.date FROM trips AS trip LEFT JOIN places AS place ON place.id = trip.place_id LEFT JOIN users AS user ON user.id = trip.user_id INNER JOIN( SELECT user_id AS clm1, MAX(date) AS clm2 FROM trips GROUP BY user_id ) AS table2 ON table2.clm2 = trip.date AND table2.clm1 = trip.user_id ORDER BY trip.id ASC |
結果
欲しいレコードが抽出出来ています。
色をつけた、INNER JOIN 部分がミソ。
ここで表題にも挙げている、GROUP BY（グループ化）とMAX（最大値の取得）を行っています。
なるほどですね。