• 資料庫程式設計
  • SQL語法
  • 常用SQL指令
  • import_contacts GROUP BY 與 HAVING
    5009
適用範圍

通常與聚合函數一起使用,以將結果集合分成一列或多列 SELECT "欄位1", 聚合函數("欄位2") FROM "資料表" GROUP BY "欄位1";

實用性:
重要性:

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);

結果:

City Qty
Mexico D.F. 2
程式設計

請選擇下方類別,查看您想了解的「程式設計」知識。