为此,请使用GROUP BY HAVING子句。
让我们首先创建一个表-mysql> create table DemoTable782 (
Name varchar(100),
Score int
);
使用插入命令在表中插入一些记录-mysql> insert into DemoTable782 values('John',156);
mysql> insert into DemoTable782 values('Carol',250);
mysql> insert into DemoTable782 values('Bob',140);
mysql> insert into DemoTable782 values('John',126);
mysql> insert into DemoTable782 values('John',140);
mysql> insert into DemoTable782 values('Bob',280);
mysql> insert into DemoTable782 values('Bob',250);
mysql> insert into DemoTable782 values('Carol',189);
mysql> insert into DemoTable782 values('Carol',299);
使用select语句显示表中的所有记录-mysql> select *from DemoTable782;
这将产生以下输出-+-------+-------+
| Name | Score |
+-------+-------+
| John | 156 |
| Carol | 250 |
| Bob | 140 |
| John | 126 |
| John | 140 |
| Bob | 280 |
| Bob | 250 |
| Carol | 189 |
| Carol | 299 |
+-------+-------+
9 rows in set (0.00 sec)
以下是按列分组并显示相似值之和的查询-mysql> select Name,SUM(Score) AS Total
from DemoTable782
group by Name
HAVING Total > 500;
这将产生以下输出-+-------+-------+
| Name | Total |
+-------+-------+
| Carol | 738 |
| Bob | 670 |
+-------+-------+
2 rows in set (0.00 sec)