SQLite – HAVING 子句

SQLite – HAVING子句

HAVING使您能够指定过滤条件哪一组结果出现在最终的结果。

WHERE子句的地方条件选定的列, 在有HAVING 子句的地方 就有GROUP BY子句包含的条件组.

语法：

下面是在一个SELECT子句的位置查询:

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

HAVING 子句必须遵循的GROUP BY子句的查询,还必须先于ORDER BY如果使用它。

下面是SELECT语句的语法,包括有条款:

SELECT column1, column2

FROM table1, table2

WHERE [ conditions ]

GROUP BY column1, column2

HAVING [ conditions ]

ORDER BY column1, column2

例子：

COMPANY table有以下记录:

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