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)