HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。
WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。
下面是 HAVING 子句在 SELECT 查询中的位置：
|
1 2 3 4 5 6 |
<span class="pln">SELECT FROM WHERE GROUP BY HAVING ORDER BY</span> |
在一个查询中，HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前。下面是包含 HAVING 子句的 SELECT 语句的语法：
|
1 2 3 4 5 6 |
<span class="pln">SELECT column1</span><span class="pun">,</span><span class="pln"> column2 FROM table1</span><span class="pun">,</span><span class="pln"> table2 WHERE </span><span class="pun">[</span><span class="pln"> conditions </span><span class="pun">]</span><span class="pln"> GROUP BY column1</span><span class="pun">,</span><span class="pln"> column2 HAVING </span><span class="pun">[</span><span class="pln"> conditions </span><span class="pun">]</span><span class="pln"> ORDER BY column1</span><span class="pun">,</span><span class="pln"> column2</span> |
假设 COMPANY 表有以下记录：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<span class="pln">ID NAME AGE ADDRESS SALARY </span><span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="lit">1</span> <span class="typ">Paul</span> <span class="lit">32</span> <span class="typ">California</span> <span class="lit">20000.0</span> <span class="lit">2</span> <span class="typ">Allen</span> <span class="lit">25</span> <span class="typ">Texas</span> <span class="lit">15000.0</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="lit">23</span> <span class="typ">Norway</span> <span class="lit">20000.0</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="lit">25</span> <span class="typ">Rich</span><span class="pun">-</span><span class="typ">Mond</span> <span class="lit">65000.0</span> <span class="lit">5</span> <span class="typ">David</span> <span class="lit">27</span> <span class="typ">Texas</span> <span class="lit">85000.0</span> <span class="lit">6</span> <span class="typ">Kim</span> <span class="lit">22</span> <span class="typ">South</span><span class="pun">-</span><span class="typ">Hall</span> <span class="lit">45000.0</span> <span class="lit">7</span> <span class="typ">James</span> <span class="lit">24</span> <span class="typ">Houston</span> <span class="lit">10000.0</span> <span class="lit">8</span> <span class="typ">Paul</span> <span class="lit">24</span> <span class="typ">Houston</span> <span class="lit">20000.0</span> <span class="lit">9</span> <span class="typ">James</span> <span class="lit">44</span> <span class="typ">Norway</span> <span class="lit">5000.0</span> <span class="lit">10</span> <span class="typ">James</span> <span class="lit">45</span> <span class="typ">Texas</span> <span class="lit">5000.0</span> |
下面是一个实例，它将显示名称计数小于 2 的所有记录：
|
1 |
<span class="pln">sqlite </span><span class="pun">></span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM COMPANY GROUP BY name HAVING count</span><span class="pun">(</span><span class="pln">name</span><span class="pun">)</span> <span class="pun"><</span> <span class="lit">2</span><span class="pun">;</span> |
这将产生以下结果：
|
1 2 3 4 5 6 7 |
<span class="pln">ID NAME AGE ADDRESS SALARY </span><span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="lit">2</span> <span class="typ">Allen</span> <span class="lit">25</span> <span class="typ">Texas</span> <span class="lit">15000</span> <span class="lit">5</span> <span class="typ">David</span> <span class="lit">27</span> <span class="typ">Texas</span> <span class="lit">85000</span> <span class="lit">6</span> <span class="typ">Kim</span> <span class="lit">22</span> <span class="typ">South</span><span class="pun">-</span><span class="typ">Hall</span> <span class="lit">45000</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="lit">25</span> <span class="typ">Rich</span><span class="pun">-</span><span class="typ">Mond</span> <span class="lit">65000</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="lit">23</span> <span class="typ">Norway</span> <span class="lit">20000</span> |
下面是一个实例，它将显示名称计数大于 2 的所有记录：
|
1 |
<span class="pln">sqlite </span><span class="pun">></span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM COMPANY GROUP BY name HAVING count</span><span class="pun">(</span><span class="pln">name</span><span class="pun">)</span> <span class="pun">></span> <span class="lit">2</span><span class="pun">;</span> |
这将产生以下结果：
|
1 2 3 4 5 |
<span class="pln">ID NAME AGE ADDRESS SALARY </span><span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="lit">10</span> <span class="typ">James</span> <span class="lit">45</span> <span class="typ">Texas</span> <span class="lit">5000 from:<a href="http://www.runoob.com/sqlite/sqlite-having-clause.html">http://www.runoob.com/sqlite/sqlite-having-clause.html</a></span> |