HAVING 是屬於一般使用者常會忽略的.
我們以sakila範例資料庫的city 資料表為例.

SELECT country
       , COUNT(1) AS cnt
  FROM city ct, country co
 WHERE ct.country_id = co.country_id 
 GROUP BY ct.country_id
 ORDER BY cnt DESC;

資料量大,不列出.
這是一般常會見到的,依據國家別來算城市數量,
並依城市數量降冪排序列出.

有時候會產生不少資料,而且後面的資料用處不大,
以這裡的情形就是有些國家在資料表裡面只有一個城市.
當我們不需要全部資料的情況下,如只要先探討佔比較高
的國家時.就可以利用計算比例以及HAVING來過濾資料.

例如

SELECT country
       , COUNT(1) AS cnt
       , ROUND(
            100 * COUNT(1) /
                (SELECT COUNT(1)
                   FROM city
                )
            ,2) AS Pct
  FROM city ct, country co
 WHERE ct.country_id = co.country_id 
 GROUP BY ct.country_id
HAVING Pct >= 1.0
 ORDER BY cnt DESC; 

+--------------------+-----+-------+
| country            | cnt | Pct   |
+--------------------+-----+-------+
| India              |  60 | 10.00 |
| China              |  53 |  8.83 |
| United States      |  35 |  5.83 |
| Japan              |  31 |  5.17 |
| Mexico             |  30 |  5.00 |
| Russian Federation |  28 |  4.67 |
| Brazil             |  28 |  4.67 |
| Philippines        |  20 |  3.33 |
| Turkey             |  15 |  2.50 |
| Indonesia          |  14 |  2.33 |
| Argentina          |  13 |  2.17 |
| Nigeria            |  13 |  2.17 |
| South Africa       |  11 |  1.83 |
| Taiwan             |  10 |  1.67 |
| United Kingdom     |   8 |  1.33 |
| Poland             |   8 |  1.33 |
| Iran               |   8 |  1.33 |
| Germany            |   7 |  1.17 |
| Venezuela          |   7 |  1.17 |
| Italy              |   7 |  1.17 |
| Canada             |   7 |  1.17 |
| Vietnam            |   6 |  1.00 |
| Colombia           |   6 |  1.00 |
| Ukraine            |   6 |  1.00 |
| Egypt              |   6 |  1.00 |
+--------------------+-----+-------+
25 rows in set (0.00 sec)

這樣就只會列出佔比超過1%的國家.
當我們對資料作分析,可以將注意力放在主要項目上.
GROUP BY 搭配聚合函數如SUM(),COUNT(),AVG()是經常
見到的應用,而HAVING子句是一個容易被忽視的配角.
適當情況下,可以讓這個配角也出來演出一下.