having

那我们如何对函数产生的值来设定条件呢？举例来说，我们可能只需要知道哪些店的营业额有超过 $1,500。在这个情况下，我们不能使用 WHERE 的指令。 那要怎么办呢？很幸运地，SQL 有提供一个 HAVING 的指令，而 我们就可以用这个指令来达到这个目标。 HAVING子句通常是在一个 SQL 句子的最后。一个含有 HAVING 子句的 SQL 并不一定要包含 GROUP BY 子句。HAVING的语法如下：

SELECT "栏位1", SUM("栏位2") 
FROM "表格名" 
GROUP BY "栏位1" 
HAVING (函数条件)

请读者注意： GROUP BY 子句并不是一定需要的。

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2012/8/19 8:45:30                            */
/*==============================================================*/


drop table if exists store_information;

drop table if exists geography;


/*==============================================================*/
/* Table: store_information                                     */
/*==============================================================*/
create table store_information
(
	 no                   int not null,
   store_name           varchar(20),
   sales                decimal(10,2),
   date                 date,
   primary key (no)
);

/*==============================================================*/
/* Table: geography                                             */
/*==============================================================*/
create table geography
(
	 no										int not null,
   region_name          varchar(20),
   store_name           varchar(20),
   primary key (no)
);

delete from store_information;
delete from geography;

insert store_information(no, store_name, sales, date) values(1, "Los Angeles", 150.2, '2008.12.01');
insert store_information(no, store_name, sales, date) values(2, "San Diego", 250.3, '2008.01.01');
insert store_information(no, store_name, sales, date) values(3, "Los Angeles", 20.2, '2008.02.01');
insert store_information(no, store_name, sales, date) values(4, "Boston", 700.2, '2008.06.11');
insert store_information(no, store_name, sales, date) values(5, "Guangzhou", 790.25, '2012.06.11');

insert geography(no, region_name, store_name) values(1, "East", "Boston");
insert geography(no, region_name, store_name) values(2, "East", "New York");
insert geography(no, region_name, store_name) values(3, "West", "Los Angeles");
insert geography(no, region_name, store_name) values(4, "West", "San Diego");

select * from store_information;
select * from geography;


mysql> select * from store_information;
+----+-------------+--------+------------+
| no | store_name  | sales  | date       |
+----+-------------+--------+------------+
|  1 | Los Angeles | 150.20 | 2008-12-01 |
|  2 | San Diego   | 250.30 | 2008-01-01 |
|  3 | Los Angeles |  20.20 | 2008-02-01 |
|  4 | Boston      | 700.20 | 2008-06-11 |
|  5 | Guangzhou   | 790.25 | 2012-06-11 |
+----+-------------+--------+------------+
5 rows in set (0.00 sec)

mysql> select * from geography;
+----+-------------+-------------+
| no | region_name | store_name  |
+----+-------------+-------------+
|  1 | East        | Boston      |
|  2 | East        | New York    |
|  3 | West        | Los Angeles |
|  4 | West        | San Diego   |
+----+-------------+-------------+

select store_name, sum(sales) 
from store_information 
group by store_name
having sum(sales) > 250

+------------+------------+
| store_name | sum(sales) |
+------------+------------+
| Boston     |     700.20 |
| Guangzhou  |     790.25 |
| San Diego  |     250.30 |
+------------+------------+