Latest web development tutorials

SQLite Having 子句

HAVING 子句允許指定條件來過濾將出現在最終結果中的分組結果。

WHERE 子句在所選列上設置條件,而HAVING 子句則在由GROUP BY 子句創建的分組上設置條件。

語法

下面是HAVING 子句在SELECT 查詢中的位置:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

在一個查詢中,HAVING 子句必須放在GROUP BY 子句之後,必須放在ORDER BY 子句之前。 下面是包含HAVING 子句的SELECT 語句的語法:

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

實例

假設COMPANY 表有以下記錄:

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
8 Paul 24 Houston 20000.0
9 James 44 Norway 5000.0
10 James 45 Texas 5000.0

下面是一個實例,它將顯示名稱計數小於2 的所有記錄:

sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;

這將產生以下結果:

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000
5 David 27 Texas 85000
6 Kim 22 South-Hall 45000
4 Mark 25 Rich-Mond 65000
3 Teddy 23 Norway 20000

下面是一個實例,它將顯示名稱計數大於2 的所有記錄:

sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;

這將產生以下結果:

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
10 James 45 Texas 5000