MySQL 如何在GROUP BY子句中按顺序或选择行

首先让我们创建一个表 –

mysql> create table DemoTable1572
 -> (
 -> StudentId int,
 -> StudentMarks int,
 -> StudentName varchar(20)
 -> );
Query OK，0 rows affected (0.56 sec) 

使用insert命令向表中插入一些记录 –

mysql> insert into DemoTable1572 values(1,79,'Sam');
Query OK，1 row affected (0.10 sec)
mysql> insert into DemoTable1572 values(2,89,'Chris');
Query OK，1 row affected (0.12 sec)
mysql> insert into DemoTable1572 values(3,98,'David');
Query OK，1 row affected (0.22 sec)
mysql> insert into DemoTable1572 values(4,79,'Bob');
Query OK，1 row affected (0.10 sec)

使用select语句显示表中的所有记录 –

mysql> select * from DemoTable1572;

这将产生以下输出 –

+-----------+--------------+-------------+
| StudentId | StudentMarks | StudentName |
+-----------+--------------+-------------+
|         1 |           79 |       Sam   |
|         2 |           89 |       Chris |
|         3 |           98 |       David |
|         4 |           79 |       Bob   |
+-----------+--------------+-------------+
4 rows in set (0.00 sec)

这是在MySQL GROUP BY子句中按顺序或选择行的查询 –

mysql> select * from DemoTable1572
 -> where StudentId in (select min(StudentId) from DemoTable1572 group by StudentMarks);

这将产生以下输出 –

+-----------+--------------+-------------+
| StudentId | StudentMarks | StudentName |
+-----------+--------------+-------------+
|         1 |           79 |       Sam   |
|         2 |           89 |       Chris |
|         3 |           98 |       David |
+-----------+--------------+-------------+
3 rows in set (0.10 sec)

阅读更多：MySQL 教程