MySQLで学生ごとの平均点を求め、最高の平均点のみを表示する方法

MySQLでは、サブクエリ（副問い合わせ）を活用することで、学生ごとの平均点を算出し、その中から最高の平均点だけを取り出すことができます。本記事では、テーブルの作成からサンプルデータの挿入、クエリの実行まで、具体的な手順をわかりやすく解説します。

テーブルの作成

まず、学生名と点数を格納するためのテーブルを作成します。

mysql> create table DemoTable
(
    StudentName varchar(40),
    StudentMarks int
);
Query OK, 0 rows affected (0.50 sec)

サンプルデータの挿入

次に、insertコマンドを使ってテーブルにレコードを挿入します。ここでは、同じ学生が複数の点数を持つデータを登録します。

mysql> insert into DemoTable values('Adam',56);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('Chris',78);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('Adam',89);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('Robert',98);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable values('Chris',65);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values('Robert',34);
Query OK, 1 row affected (0.20 sec)

登録データの確認

select文でテーブル内の全レコードを表示し、データが正しく登録されているか確認しましょう。

mysql> select *from DemoTable;

以下の出力が得られます。

+-------------+--------------+
| StudentName | StudentMarks |
+-------------+--------------+
| Adam        |           56 |
| Chris       |           78 |
| Adam        |           89 |
| Robert      |           98 |
| Chris       |           65 |
| Robert      |           34 |
+-------------+--------------+
6 rows in set (0.00 sec)

最高の平均点を取得するクエリ

学生ごとの平均点を求め、その中で最も高い平均点のみを表示するには、以下のクエリを実行します。

mysql> select max(avSal.StudentMarks)
from (select avg(StudentMarks) as `StudentMarks` from DemoTable group by StudentName) as avSal;

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

+-------------------------+
| max(avSal.StudentMarks) |
+-------------------------+
|                 72.5000 |
+-------------------------+
1 row in set (0.09 sec)

クエリの仕組み

このクエリのポイントは、サブクエリの部分です。内側のサブクエリでは、GROUP BY StudentNameによって学生ごとにレコードをグループ化し、AVG()関数で各学生の平均点を計算しています。

各学生の平均点は以下のように計算されます。

• Adam：(56 + 89) ÷ 2 = 72.5
• Chris：(78 + 65) ÷ 2 = 71.5
• Robert：(98 + 34) ÷ 2 = 66.0

外側のクエリでは、この平均点の一覧に対してMAX()関数を適用することで、最高の平均点である「72.5」だけを取得しています。このように、サブクエリと集計関数を組み合わせることで、グループ化された結果に対してさらに集計処理を行うことが可能になります。