GROUP BY 與 HAVING
GROUP BY通常與聚合函數（COUNT，MAX，MIN，SUM，AVG）一起使用，以將結果集合分成一列或多列。
如果要針對 GROUP BY 後的資料做設定條件，就要用搭配 HAVING 。
針對以下客戶資料表，只要查詢城市City ,城市數量Qty， 並以City城市進行各別加總,
SELECT City,count(City)as qty FROM Customers GROUP BY City;
| CustomerID |
CompanyName |
ContactName |
Address |
City |
PostalCode |
Country |
| 1 |
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
| 2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitucion 2222 |
Mexico D.F. |
05021 |
Mexico |
| 3 |
Antonio Moreno Taqueria |
Antonio Moreno |
Mataderos 2312 |
Mexico D.F. |
05023 |
Mexico |
| 4 |
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
| 5 |
Berglunds snabbkop |
Christina Berglund |
Berguvsvagen 8 |
Lulea |
S-958 22 |
Sweden |
| 6 |
Blauer See Delikatessen |
Hanna Moos |
Forsterstr. 57 |
Mannheim |
68306 |
Germany |
| 7 |
Blondesddsl pere et fils |
Frederique Citeaux |
24, place Kleber |
Strasbourg |
67000 |
France |
| 8 |
Bolido Comidas preparadas |
Martin Sommer |
C/ Araquil, 67 |
Madrid |
28023 |
Spain |
| 9 |
Bon app' |
Laurence Lebihan |
12, rue des Bouchers |
Marseille |
13008 |
France |
| 10 |
Bottom-Dollar Markets |
Elizabeth Lincoln |
23 Tsawassen Blvd. |
Tsawassen |
T2F 8M4 |
Canada |
| 11 |
Du monde entier |
Janine Labrune |
67, rue des Cinquante Otages |
Nantes |
44000 |
France |
結果:
| City |
Qty |
| Berlin |
1 |
| Mexico D.F. |
2 |
| London |
1 |
| Lulea |
1 |
| Mannheim |
1 |
| Strasbourg |
1 |
| Madrid |
1 |
| Marseille |
1 |
| Tsawassen |
1 |
| Nantes |
1 |
將以上得到的結果做條件式查詢
HAVING
SELECT City,count(City)as qty FROM Customers GROUP BY City HAVING (count(City)>1);
結果: