MySQLで特定の列の最大値を取得し、同じ列の他のすべての値に設定する方法

概要

MySQLでは、MAX()関数とスカラサブクエリを組み合わせることで、ある列の最大値を取得し、その値を同じ列の他のすべての行に設定・表示することができます。ここでは、名前とスコアを格納するテーブルを例に、具体的な手順をわかりやすく解説します。

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

まず、テーブルを作成しましょう。

mysql> create table DemoTable
(
    FirstName varchar(100),
    Score int
);
Query OK, 0 rows affected (0.60 sec)

2. レコードの挿入

INSERT文を使ってテーブルにデータを追加します。

mysql> insert into DemoTable values('David',59);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('Chris',97);
Query OK, 1 row affected (0.24 sec)
mysql> insert into DemoTable values('Bob',98);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('Carol',91);
Query OK, 1 row affected (0.11 sec)

3. テーブルの内容を確認する

SELECT文ですべてのレコードを表示してみましょう。

mysql> select *from DemoTable;

実行結果は次のとおりです。

+-----------+-------+
| FirstName | Score |
+-----------+-------+
| David     |    59 |
| Chris     |    97 |
| Bob       |    98 |
| Carol     |    91 |
+-----------+-------+
4 rows in set (0.00 sec)

4. 最大値を取得して全行に適用するクエリ

以下が、列の最大値を取得し、同じ列の他のすべての値として表示するためのクエリです。

mysql> select FirstName,(select max(Score) from DemoTable) as Score from DemoTable;

実行結果は次のようになります。

+-----------+-------+
| FirstName | Score |
+-----------+-------+
| David     |    98 |
| Chris     |    98 |
| Bob       |    98 |
| Carol     |    98 |
+-----------+-------+
4 rows in set (0.00 sec)

クエリの仕組み

ポイントは、SELECT句の中で使われているスカラサブクエリ (select max(Score) from DemoTable) です。このサブクエリはテーブル全体を対象に Score 列の最大値「98」を1つ返し、その結果がエイリアス Score として各行に出力されます。そのため、元のスコアの値に関係なく、すべての行に最大値が表示される仕組みです。

補足：実際にテーブルの値を更新したい場合

表示を置き換えるだけでなく、テーブル自体の値を最大値で更新したい場合には、UPDATE文とサブクエリを組み合わせます。

mysql> update DemoTable
    set Score = (select max(Score) from (select * from DemoTable) as tmp);
Query OK, 4 rows affected (0.10 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MySQLでは、UPDATE対象のテーブルと同じテーブルをサブクエリ内で直接参照することができません。そこで、一度派生テーブル（tmp）としてラップしてから参照するのが定番のテクニックです。このクエリを実行すると、すべての行の Score が最大値「98」に一括で更新されます。