MySQLでGROUP BYとHAVINGを使って、最大日付付きの重複値を抽出する方法

MySQLで「同じ名前が複数回登場するレコード」の中から、それぞれのグループにおける最大の日付を取得したいケースはよくあります。このような場合には、GROUP BY句とHAVING句を組み合わせることで実現できます。

本記事では、サンプルテーブルを作成し、実際にデータを挿入したうえで、重複値ごとの最大日付を表示するクエリの書き方を解説します。

1. サンプルテーブルの作成

まずは、学生名と期日を格納するテーブルを作成しましょう。

mysql> create table DemoTable
    (
    StudentName varchar(100),
    DueDate date
    );
Query OK, 0 rows affected (0.72 sec)

テーブルには StudentName(学生名)と DueDate(期日)の2つのカラムを定義しています。

2. テストデータの挿入

次に、INSERTコマンドを使ってレコードを挿入します。ここでは、同じ学生名が複数回登場するデータを意図的に用意しています。

mysql> insert into DemoTable values('John','2019-01-11');
Query OK, 1 row affected (0.26 sec)
mysql> insert into DemoTable values('Chris','2019-02-11');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values('Chris','2019-03-11');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable values('John','2019-04-11');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('Bob','2019-05-11');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('Bob','2019-06-11');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('Robert','2019-07-11');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable values('Robert','2019-08-11');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('Robert','2019-09-11');
Query OK, 1 row affected (0.15 sec)

3. 全レコードの確認

SELECT文を使って、テーブル内のすべてのレコードを確認してみましょう。

mysql> select *from DemoTable;

出力結果

+-------------+------------+
| StudentName | DueDate    |
+-------------+------------+
| John        | 2019-01-11 |
| Chris       | 2019-02-11 |
| Chris       | 2019-03-11 |
| John        | 2019-04-11 |
| Bob         | 2019-05-11 |
| Bob         | 2019-06-11 |
| Robert      | 2019-07-11 |
| Robert      | 2019-08-11 |
| Robert      | 2019-09-11 |
+-------------+------------+
9 rows in set (0.00 sec)

このように、JohnとChrisは2件ずつ、Bobも2件、Robertは3件存在していることがわかります。

4. 重複値ごとに最大日付を表示するクエリ

それでは本題です。以下のクエリを実行すると、学生名ごとにグループ化し、そのうちレコード数が2件以上(つまり重複している)グループのみを対象として、最大の日付を表示できます。

mysql> select tbl.StudentName,max(tbl.DueDate) from DemoTable tbl group by tbl.StudentName
having count(*) > 1;

ポイントは以下の2点です。

• GROUP BY: 学生名(StudentName)ごとにレコードをグループ化します。
• HAVING count(*) > 1: グループ化後の各グループに対して条件を適用し、レコードが複数存在する(重複している)グループだけを抽出します。

出力結果

+-------------+------------------+
| StudentName | max(tbl.DueDate) |
+-------------+------------------+
| John        | 2019-04-11       |
| Chris       | 2019-03-11       |
| Bob         | 2019-06-11       |
| Robert      | 2019-09-11       |
+-------------+------------------+
4 rows in set (0.00 sec)

結果を見ると、重複しているすべての学生名について、それぞれの最新(最大)の日付が正しく表示されていることが確認できます。例えばRobertの場合、3件のレコードのうち最も新しい 2019-09-11 が返されています。

まとめ

重複値ごとに最大日付を取得したい場合は、GROUP BYでキーとなるカラムを指定し、HAVING count(*) > 1で重複グループだけに絞り込み、MAX()関数で最大値を取得するのが基本のパターンです。WHEREではなくHAVINGを使う点が重要で、これは集計後のグループに対して条件を適用するためです。このパターンは、顧客ごとの最新注文日や、ユーザーごとの直近ログイン日時などを求める際にも応用できる、非常に実用的なテクニックです。