【MySQL】別テーブルの列をGROUP BYして新しいテーブルを作成する方法

はじめに

既存のテーブルの特定の列（カラム）に対して GROUP BY を実行し、その結果をもとに新しいテーブルを作成したい場合は、CREATE TABLE ... AS SELECT ステートメントが便利です。

ここでは、国名が格納されたテーブルから重複を取り除いたデータを持つ新しいテーブルを作成する手順を、具体例とともに解説します。

元となるテーブルの作成

まず、サンプル用のテーブル「DemoTable1」を作成します。

mysql> create table DemoTable1
    -> (
    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> CountryName varchar(20)
    -> );
Query OK, 0 rows affected (0.68 sec)

レコードの挿入

続いて、INSERT コマンドでいくつかのレコードを追加します。同じ国名を意図的に複数回登録している点に注目してください。

mysql> insert into DemoTable1(CountryName) values('US');
Query OK, 1 row affected (0.15 sec)

mysql> insert into DemoTable1(CountryName) values('UK');
Query OK, 1 row affected (0.27 sec)

mysql> insert into DemoTable1(CountryName) values('AUS');
Query OK, 1 row affected (0.11 sec)

mysql> insert into DemoTable1(CountryName) values('UK');
Query OK, 1 row affected (0.13 sec)

mysql> insert into DemoTable1(CountryName) values('UK');
Query OK, 1 row affected (0.06 sec)

mysql> insert into DemoTable1(CountryName) values('US');
Query OK, 1 row affected (0.10 sec)

mysql> insert into DemoTable1(CountryName) values('AUS');
Query OK, 1 row affected (0.10 sec)

登録内容の確認

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

mysql> select *from DemoTable1;

出力結果

+----+-------------+
| Id | CountryName |
+----+-------------+
| 1  | US          |
| 2  | UK          |
| 3  | AUS         |
| 4  | UK          |
| 5  | UK          |
| 6  | US          |
| 7  | AUS         |
+----+-------------+
7 rows in set (0.00 sec)

US・UK・AUS の3種類の国名が、それぞれ複数回登録されていることがわかります。

GROUP BY を使った新テーブルの作成

それでは、別のテーブルの列に対して GROUP BY を適用し、その結果を新しいテーブルとして作成するクエリを見てみましょう。

mysql> create table DemoTable2 AS select *from DemoTable group by CountryName;
Query OK, 3 rows affected (0.79 sec)
Records: 3 Duplicates: 0 Warnings: 0

このクエリにより、「DemoTable1」のデータが国名ごとにグループ化され、その結果が新しいテーブル「DemoTable2」に格納されます。

新テーブルの確認

作成されたテーブルの中身を確認します。

mysql> select *from DemoTable2;

出力結果

+----+-------------+
| Id | CountryName |
+----+-------------+
| 1  | US          |
| 2  | UK          |
| 3  | AUS         |
+----+-------------+
3 rows in set (0.00 sec)

重複していたレコードがまとめられ、US・UK・AUS の3行だけが残っていることが確認できます。

注意点

MySQL 5.7 以降では、SQLモード「ONLY_FULL_GROUP_BY」がデフォルトで有効になっています。このモードでは、集計関数を使わずに SELECT * と GROUP BY を組み合わせるとエラーが発生する場合があります。その際は、必要な列だけを選択するか、ANY_VALUE() 関数を利用するか、単純に重複排除が目的であれば DISTINCT を使う方法も検討するとよいでしょう。