GROUP BY cột từ bảng khác trong MySQL

Để làm điều này, bạn có thể dùng câu lệnh CREATE TABLE AS SELECT. Tạo bảng đầu tiên:

mysql> CREATE TABLE DemoTable1 ( -> Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> CountryName VARCHAR(20) -> ); Query OK, 0 rows affected (0.68 sec)

Chèn bản ghi:

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)

Hiển thị:

mysql> SELECT * FROM DemoTable1;

Kết quả

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

Truy vấn GROUP BY cột vào bảng mới:

mysql> CREATE TABLE DemoTable2 AS SELECT * FROM DemoTable1 GROUP BY CountryName; Query OK, 3 rows affected (0.79 sec) Records: 3 Duplicates: 0 Warnings: 0

Kiểm tra bảng mới:

mysql> SELECT * FROM DemoTable2;

Kết quả

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