SQL having 子句示例 - 使用Sqlite演示

HAVING 子句
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。

也就是说sql使用了聚合函数获取到数据以后，此时不能用where再筛选其中的数据，此时使用having；

现有 订单表；
查找订单总金额少于 2000 的客户；
查找客户 "Bush" 或 "Adams" 拥有超过 1500 的订单总金额；
2次查询结果如下图；

现有 学生表 如下；
查找每个老师的学生的平均年龄且平均年龄大于12；
不使用HAVING和使用having两种写法查询结果如下；不使用having将使用一个子查询；
 

使用Sqlite3；全部的cmd操作过程如下；含sql语句；

Microsoft Windows [版本 6.1.7601]
Copyright (c) 2010 Microsoft Corporation.  All rights reserved.

S:\sqlite>sqlite3 wctest1.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .database
seq  name             file

---  ---------------  ----------------------------------------------------------

0    main             S:\sqlite\wctest1.db

sqlite> create table Persons(
   ...> Id_P int primary key not null,
   ...> LastName varchar(255),
   ...> FirstName varchar(255),
   ...> Address varchar(255),
   ...> City varchar(255));
sqlite> INSERT INTO Persons VALUES (1,'Gates', 'Bill', 'Xuanwumen 10', 'Beijing'
);
sqlite> INSERT INTO Persons VALUES (2,'Carter', 'Thomas', 'Street', 'Beijing');
sqlite> INSERT INTO Persons (Id_P,LastName, Address) VALUES (3,'Wilson', 'Champs
-Elysees')
   ...> ;
sqlite> create table Orders(
   ...> O_Id int primary key not null,
   ...> OrderDate date,
   ...> OrderPrice int,
   ...> Customer varchar(255));
sqlite> ^C
S:\sqlite>INSERT INTO Persons VALUES (1,'2008/12/29', 1000, 'Bush');
'INSERT' 不是内部或外部命令，也不是可运行的程序
或批处理文件。

S:\sqlite>sqlite3 wctest1.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> INSERT INTO Orders VALUES (1,'2008/12/29', 1000, 'Bush');
sqlite> INSERT INTO Orders VALUES (2,'2008/11/23', 1600, 'Carter');
sqlite> INSERT INTO Orders VALUES (3,'2008/10/05', 700, 'Bush');
sqlite> INSERT INTO Orders VALUES (4,'2008/09/28', 300, 'Bush');
sqlite> INSERT INTO Orders VALUES (5,'2008/08/06', 2000, 'Adams');
sqlite> INSERT INTO Orders VALUES (6,'2008/07/21', 100, 'Carter');
sqlite> select * from Orders;
1|2008/12/29|1000|Bush
2|2008/11/23|1600|Carter
3|2008/10/05|700|Bush
4|2008/09/28|300|Bush
5|2008/08/06|2000|Adams
6|2008/07/21|100|Carter
sqlite> SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM
(OrderPrice)<2000;
Carter|1700
sqlite> SELECT Customer,SUM(OrderPrice) FROM Orders WHERE Customer='Bush' OR Cus
tomer='Adams'GROUP BY Customer HAVING SUM(OrderPrice)>1500;
Adams|2000
Bush|2000
sqlite> create table Students(
   ...> Id int primary key not null,
   ...> TeacherID int,
   ...> Name varchar(255),
   ...> Age int);
sqlite> insert into Students values (1,4,'小明',10);
sqlite> insert into Students values (2,5,'小白',12);
sqlite> insert into Students values (3,4,'小红',11);
sqlite> insert into Students values (4,5,'小白1',14);
sqlite> insert into Students values (5,4,'小红1',15);
sqlite> insert into Students values (6,6,'小明2',10);
sqlite> insert into Students values (7,6,'小白2',12);
sqlite> insert into Students values (8,6,'小红2',11);
sqlite> insert into Students values (9,5,'小白2',14);
sqlite> insert into Students values (10,4,'小红2',15);
sqlite> select * from Students;
1|4|小明|10
2|5|小白|12
3|4|小红|11
4|5|小白1|14
5|4|小红1|15
6|6|小明2|10
7|6|小白2|12
8|6|小红2|11
9|5|小白2|14
10|4|小红2|15
sqlite> SELECT * FROM (SELECT TeacherID, AVG(Age) AS AGE FROM Student GROUP BY T
eacherID) T WHERE T.AGE > 12;
Error: no such table: Student
sqlite> SELECT * FROM (SELECT TeacherID, AVG(Age) AS AGE FROM Students GROUP BY
TeacherID) T WHERE T.AGE > 12;
4|12.75
5|13.3333333333333
sqlite> SELECT TeacherID, AVG(Age) AS AGE FROM Student GROUP BY TeacherID HAVING
 AVG(Age) > 12;
Error: no such table: Student
sqlite> SELECT TeacherID, AVG(Age) AS AGE FROM Students GROUP BY TeacherID HAVIN
G AVG(Age) > 12;
4|12.75
5|13.3333333333333
sqlite>