使用HAVING运算符进行简单查询
我有一个Employees表，它包含两列：Employee和DepartmentId，如下所示
|Employee | DepartmentId
-------------------------
| e1 | 1
| e2 | 1
| e3 | 1
| e4 | 2
| e5 | 2
| e6 | 3
| e7 | 3
| e8 | 3
| e9 | 4
| e10 | 5
| e11 | 6
SELECT Department,
COUNT(Employee) as Quantity
FROM Employees
GROUP BY Department
HAVING (Quantity > 3)
ORDER BY Department
SELECT Department
FROM (SELECT Department, COUNT(Employee) AS Quantity
FROM Employees
GROUP BY Department)
WHERE Quantity > 5
没有找到相关结果
已邀请:
3 个回复
械怒等
很好。数据库将仅评估表达式一次，并在select和having子句中重用结果。 您在子查询中选择值的示例也很好（除了您需要子查询的别名）。这里不需要使用这种技术，但是如果表达式更复杂并且您希望避免因可维护性原因而重复代码，那么它可能很有用。 请注意，在MySQL中，您要编写的查询将起作用。但是这个MySQL特定的扩展和相同的技术在SQL Server中不起作用。伐教爸
SELECT Department, COUNT(Employee) AS cnt FROM Employees GROUP BY Department HAVING COUNT(Employee) > 5钨蜡唤喉晤