HAVING 子句
在 SQL 中增加 HAVING 子句原因是，WHERE 關(guān)鍵字無(wú)法與聚合函數(shù)一起使用。

HAVING 子句可以讓我們篩選分組后的各組數(shù)據(jù)。

也就是說(shuō)sql使用了聚合函數(shù)獲取到數(shù)據(jù)以后，此時(shí)不能用where再篩選其中的數(shù)據(jù)，此時(shí)使用having；

現(xiàn)有 訂單表；
查找訂單總金額少于 2000 的客戶(hù)；
查找客戶(hù) "Bush" 或 "Adams" 擁有超過(guò) 1500 的訂單總金額；
2次查詢(xún)結(jié)果如下圖；

現(xiàn)有 學(xué)生表 如下；
查找每個(gè)老師的學(xué)生的平均年齡且平均年齡大于12；
不使用HAVING和使用having兩種寫(xiě)法查詢(xún)結(jié)果如下；不使用having將使用一個(gè)子查詢(xún)；
?

使用Sqlite3；全部的cmd操作過(guò)程如下；含sql語(yǔ)句；

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' 不是內(nèi)部或外部命令，也不是可運(yùn)行的程序
或批處理文件。

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>

總結(jié)

以上是生活随笔為你收集整理的SQL having 子句示例 - 使用Sqlite演示的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。