MySQL 如何执行有条件的GROUP BY以获取数据

首先，让我们创建一个表 –

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

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

mysql> insert into DemoTable values('John',78);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('Chris',48);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('John',67);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('Chris',89);
Query OK, 1 row affected (0.15 sec)

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

mysql> select *from DemoTable;

这将产生以下输出。在这里，我们有一些重复的学生名称，如成绩为78和67的学生“John” –

+-------------+--------------+
| StudentName | StudentMarks |
+-------------+--------------+
| John        |           78 |
| Chris       |           48 |
| John        |           67 |
| Chris       |           89 |
+-------------+--------------+
4 rows in set (0.00 sec)

以下是执行条件GROUP BY并显示具有最大分数的不同学生名称的查询。例如，具有最大分数78的学生“John” –

mysql> select StudentName,Max(StudentMarks) AS StudentMarks from DemoTable group by StudentName;

这将产生以下输出 –

+-------------+--------------+
| StudentName | StudentMarks |
+-------------+--------------+
| John        |           78 |
| Chris       |           89 |
+-------------+--------------+
2 rows in set (0.00 sec)

阅读更多：MySQL 教程