SQL中GROUP BY、HAVING操作

首先配置一个测试的数据库

-- init.sql/*auth = Parle*/drop database if exists test_db;create database test_db;use test_db;create table test_table(    `class` varchar(5) not null ,    `number` int not null,    `describe` varchar(10) not null) engine=innodb default charset=utf8;insert into test_table (`class`, `number`, `describe`) values ('a', 11, 'good');insert into test_table (`class`, `number`, `describe`) values ('a', 2, 'normal');insert into test_table (`class`, `number`, `describe`) values ('a', 19, 'bad');insert into test_table (`class`, `number`, `describe`) values ('b', 17, 'normal');insert into test_table (`class`, `number`, `describe`) values ('b', 21, 'good');insert into test_table (`class`, `number`, `describe`) values ('b', 8, 'bad');insert into test_table (`class`, `number`, `describe`) values ('c', 5, 'excellent');insert into test_table (`class`, `number`, `describe`) values ('c', 3, 'good');insert into test_table (`class`, `number`, `describe`) values ('c', 14, 'bad');

1、GROUP BY

当我们选中多栏，并对其中一栏使用函数操作，想返回依据某一栏分类的结果，就需要用到group by，废话不多说，直接操作

mysql> select * from test_table;+-------+--------+-----------+| class | number | describe  |+-------+--------+-----------+| a     |     11 | good      || a     |      2 | normal    || a     |     19 | bad       || b     |     17 | normal    || b     |     21 | good      || b     |      8 | bad       || c     |      5 | excellent || c     |      3 | good      || c     |     14 | bad       |+-------+--------+-----------+9 rows in set (0.00 sec)mysql> select class, sum(number) a    -> from test_table    -> group by class;+-------+-------+| class | count |+-------+-------+| a     |    32 || b     |    46 || c     |    22 |+-------+-------+3 rows in set (0.00 sec)

使用group by需要确定我们有group by所有其他的栏位。换句话说，除了有包括函数的栏位外，我们都需要将其放在group by 的子句中。（注意，此处descibe是MySQL的关键字，必须用反引号）

mysql> select class, `describe`, sum(number) as result    -> from test_table    -> group by class, `describe`;</span><span style="font-size:14px;"><span style="font-size:12px;">+-------+-----------+--------+| class | describe  | result |+-------+-----------+--------+| a     | bad       |     19 || a     | good      |     11 || a     | normal    |      2 || b     | bad       |      8 || b     | good      |     21 || b     | normal    |     17 || c     | bad       |     14 || c     | excellent |      5 || c     | good      |      3 |+-------+-----------+--------+9 rows in set (0.00 sec)

2、HAVING

如果需要对函数产生的值来设定条件，举例来说，我们可能只需要知道哪些class的sum结果大于40在这个情况下，我们不能使用 where的指令，因为数where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。而having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

mysql> select class, sum(number) as result    -> from test_table    -> group by class    -> <span style="color:#FF0000;">having sum(number)>40;</span>+-------+--------+| class | result |+-------+--------+| b     |     46 |+-------+--------+1 row in set (0.00 sec)