MySQLでGROUP BYとMAX()を使って学生ごとのMarks1・Marks2の最高得点を取得する方法

学生ごとのMarks1およびMarks2の最高得点を取得するには、MAX()関数とGROUP BY句を組み合わせて使用します。MAX()は、グループごとに最も大きい値を返す集計関数です。ここでは、学生のメールアドレスごとにレコードをグループ化し、各科目の最高得点を求めます。

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

まず、以下のようにテーブルを作成します。

mysql> create table DemoTable
    -> (
    -> StudentEmailId varchar(20),
    -> Marks1 int,
    -> Marks2 int
-> );
Query OK, 0 rows affected (0.90 sec)

2. レコードの挿入

INSERTコマンドを使用して、テーブルにいくつかのレコードを挿入します。

mysql> insert into DemoTable values('John@gmail.com',45,32);
Query OK, 1 row affected (0.30 sec)
mysql> insert into DemoTable values('John@gmail.com',32,45);
Query OK, 1 row affected (0.34 sec)
mysql> insert into DemoTable values('Carol@gmail.com',32,45);
Query OK, 1 row affected (1.64 sec)
mysql> insert into DemoTable values('David@gmail.com',45,32);
Query OK, 1 row affected (0.18 sec)

3. テーブル内の全レコードを表示

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

mysql> select *from DemoTable;

実行結果は以下の通りです。

+-----------------+--------+--------+
| StudentEmailId  | Marks1 | Marks2 |
+-----------------+--------+--------+
| John@gmail.com  |      45 |     32 |
| John@gmail.com  |      32 |     45 |
| Carol@gmail.com |      32 |     45 |
| David@gmail.com |      45 |     32 |
+-----------------+--------+--------+
4 rows in set (0.00 sec)

John@gmail.comのように、同じ学生が複数のレコードを持っている場合があります。この状態では、どの得点がその学生の最高得点なのか一目では分かりにくくなっています。

4. 学生ごとの最高得点を取得するクエリ

Marks1とMarks2のレコードを持つ学生ごとの最高得点を取得するには、次のクエリを実行します。

mysql> select StudentEmailId,max(Marks1),max(Marks2) from DemoTable
    -> group by StudentEmailId;

実行結果は以下の通りです。

+-----------------+-------------+-------------+
| StudentEmailId  | max(Marks1) | max(Marks2) |
+-----------------+-------------+-------------+
| John@gmail.com  |          45 |          45 |
| Carol@gmail.com |          32 |          45 |
| David@gmail.com |          45 |          32 |
+-----------------+-------------+-------------+
3 rows in set (0.00 sec)

クエリの解説

GROUP BY StudentEmailIdによって、メールアドレス（＝学生）ごとにレコードがグループ化され、各グループに対してmax(Marks1)とmax(Marks2)が計算されます。

例えばJohn@gmail.comの場合、Marks1には「45」と「32」の2つのレコードがあるため最大値は45、Marks2にも「32」と「45」のレコードがあるため最大値は45となります。その結果、Johnの最高得点は両科目とも45と表示されます。

応用：両科目を通じた最高得点を1つだけ取得したい場合

Marks1とMarks2のどちらか大きい方の値を1つのカラムとして取得したい場合は、GREATEST()関数を組み合わせると便利です。

mysql> select StudentEmailId,
    -> greatest(max(Marks1), max(Marks2)) AS MaxMarks
    -> from DemoTable
    -> group by StudentEmailId;

このクエリにより、各学生のMarks1とMarks2全体を通じての最高得点を1つの値として取得できます。GROUP BYと集計関数を組み合わせることで、このようなグループ単位の集計処理を簡単に実現できます。