SQLite HAVING

SQLite HAVING 子句用于通过聚合函数指定条件。请注意，SQLite WHERE 子句不能用于通过聚合函数指定条件。

语法

在 SQLite 中使用 HAVING 子句的语法如下:

SELECT column_name(s)
FROM table_name
WHERE condition(s)
GROUP BY column_name(s)
HAVING condition(s)
ORDER BY column_name(s); 

请注意，查询中 HAVING 子句必须位于 GROUP BY 子句之后，如果使用，还必须位于 ORDER BY 子句之前.

示例:

考虑一个数据库，其中包含名为 Employee 和 Bonus_Paid 的表，并具有以下记录:

表 1:Employee表

表 2:Bonus_Paid 表

要获取包含每个城市的员工数量（来自 Employee 表）的列表，并且仅包含拥有超过一名员工的城市，查询如下:

SELECT COUNT(EmpID) AS Number_of_Employee, City  
FROM Employee
GROUP BY City
HAVING COUNT(EmpID) > 1; 

这将产生如下所示的结果:

要根据员工数量按降序排列上述表格，查询将是:

SELECT COUNT(EmpID), City  
FROM Employee
GROUP BY City
HAVING COUNT(EmpID) > 1
ORDER BY COUNT(EmpID) DESC; 

查询结果将是:

使用 HAVING 子句with JOIN:要获取包含每个城市向员工支付的奖金的列表，并且仅包含拥有超过一名员工的城市，查询如下:

SELECT A.City, SUM(B.Bonus) AS BonusAmount
FROM Employee AS A
INNER JOIN Bonus_Paid AS B
ON A.EmpID = B.EmpID
GROUP BY City
HAVING COUNT(A.EmpID) > 1; 

此结果以下代码为: