HAVING 子句允許指定條件來過濾將出現(xiàn)在最終結(jié)果中的分組結(jié)果。
WHERE 子句在所選列上設(shè)置條件,而 HAVING 子句則在由 GROUP BY 子句創(chuàng)建的分組上設(shè)置條件。
下面是 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
假設(shè) 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
下面是一個實例,它將顯示名稱計數(shù)小于 2 的所有記錄:
sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;
這將產(chǎn)生以下結(jié)果:
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
下面是一個實例,它將顯示名稱計數(shù)大于 2 的所有記錄:
sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;
這將產(chǎn)生以下結(jié)果:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
10 James 45 Texas 5000