Having和Group by的使用

在介绍GROUP BY 和 HAVING 子句前，我们必需先讲讲sql语言中一种特殊的函数:聚合函数，例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。

SELECT SUM(population) FROM bbc

这里的SUM作用在所有返回记录的population字段上，结果就是该查询只返回一个结果，即所有国家的总人口数。

通过使用GROUP BY 子句，可以让SUM 和 COUNT 这些函数对属于一组的数据起作用。当你指定 GROUP BY region 时，属于同一个region(地区)的一组数据将只能返回一行值，也就是说，表中所有除region(地区)外的字段，只能通过 SUM, COUNT等聚合函数运算后返回一个值。

HAVING子句可以让我们筛选成组后的各组数据，WHERE子句在聚合前先筛选记录.也就是说作用在GROUP BY 子句和HAVING子句前.

而 HAVING子句在聚合后对组记录进行筛选。

让我们还是通过具体的实例来理解GROUP BY 和 HAVING 子句。

SQL实例:

一、显示每个地区的总人口数和总面积:

SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region

先以region把返回记录分成多个组，这就是GROUP BY的字面含义。分完组后，然后用聚合函数对每组中的不同字段(一或多条记录)作运算。

二、显示每个地区的总人口数和总面积.仅显示那些面积超过1000000的地区。

SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000

在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。

相反，HAVING子句可以让我们筛选成组后的各组数据

实例二

步骤一、使用如下sql脚本在数据库tempdb中建立表test，再插入10条记录

use tempdb

create table test

(

id int not null primary key,

name varchar(10),

fruit varchar(10),

amount int not null

)

insert into test values(1,'a','apple',90)

insert into test values(2,'a','orange',100)

insert into test values(3,'b','apple',110)

insert into test values(4,'b','orange',120)

insert into test values(5,'b','pear',130)

insert into test values(6,'c','apple',80)

insert into test values(7,'c','orange',100)

insert into test values(8,'c','pear',120)

insert into test values(9,'d','grape',120)

insert into test values(10,'e','apple',80)

下图为表test的设计图和记录

步骤二、HAVING和GROUP BY有关的统计查询

1. 统计水果总数大于100的人名和水果总数，并按降序排序显示，如下图所示：

相应的SQL语句(注释中的数字为执行顺序)为：

select name,sum(amount) as total/*(4)*/

from test/*(1)*/

group by name/*(2)*/

having sum(amount)>100/*不能为having total>100*/

order by total desc;/*(5)*/

    注意：不能用select id, fruit，因为根据name分的组，组内各条记录的id,fruit字段是不同的。

2. 统计水果总数大于100的人名和这些人所拥有的数量大于100的水果种数，并按升序排序，如下所示：
    name    total_more_100
       c                    1
        d                    1
        b                    3
     相应的SQL语句为：

select name,count(*) as total_more_100/*(4)*/

from test/*(1)*/

where amount>100/*(2)*/

group by name/*(3)*/

order by total_more_100 asc;/*(5)*/

3. 查找某个人的名字，他所拥有的各种水果数量都大于100，结果应如下所示：
    name
      b
       d
    相应的SQL语句为：

归纳：
     看到HAVING和GROUP BY，想到聚合函数，聚合函数作用在多条记录上，返回一个结果。WHERE在聚合前或者两者之前先筛选记录，HAVING子句在聚合后对组记录筛选。

Having和Group by的使用

有话要说