英文:
How is the having clause creating the correct output in this example?
问题
HAVING子句在SQLite中的工作原理是这样的：它用于在对分组进行聚合后进行过滤。在你提供的代码中，HAVING子句用于过滤具有max(salary)为假值的分组。然而，由于你的查询中没有指定max(salary)的条件，所以HAVING子句实际上没有起到过滤的作用。
你的第一个查询和第二个查询产生相同的输出，是因为它们实际上是等价的。第一个查询使用了HAVING子句来过滤分组，而第二个查询使用了子查询来选择每个部门中薪水最高的人员。两个查询的结果是相同的，都返回每个部门中薪水最高的人员的姓名和部门。
所以，你可以选择使用任何一个查询来获取相同的结果。
英文:
How the HAVING clause works in SQLite? My code works and returns the name and division of the highest paid person in each division. However, the use of the HAVING clause does not make sense to me since it should simply filter the groups with a false value for max(salary):
CREATE TABLE salaries AS
SELECT 'Alpha' as name, 'computer' as division, 500 as salary UNION
SELECT 'Bravo', 'computer', 600 UNION
SELECT 'Charlie', 'accounting', 200 UNION
SELECT 'Delta', 'accounting', 300 UNION
SELECT 'Echo', 'management', 800 UNION
SELECT 'Foxtrot', 'management', 900;
SELECT name, division FROM salaries GROUP BY division HAVING max(salary);
Why does the above query produce the same output as this query:
SELECT name, division
FROM salaries AS s
WHERE salary = (
SELECT MAX(salary)
FROM salaries
WHERE division = s.division
);
答案1
得分: 0
然而，对我来说，使用HAVING子句并没有太多意义，因为它只是简单地过滤掉最大薪水为false的组。
只有当每个组的最大薪水为0时，才会这样。
如果指定了HAVING子句，则将其作为布尔表达式对每个行组进行一次评估。如果评估HAVING子句的结果为false，则丢弃该组。如果HAVING子句是聚合表达式，则在组中的所有行上进行评估。如果HAVING子句是非聚合表达式，则相对于从组中任意选择的行进行评估。HAVING表达式可以引用结果中不存在的值，甚至是聚合函数。https://www.sqlite.org/lang_select.html#generation_of_the_set_of_result_rows
因此，如果你使用了以下语句：
CREATE TABLE salaries AS
SELECT 'Alpha' as name, 'computer' as division, 500 as salary UNION
SELECT 'Bravo', 'computer', 600 UNION
SELECT 'Charlie', 'accounting', 200 UNION
SELECT 'Delta', 'accounting', 300 UNION
SELECT 'Echo', 'management', 800 UNION
SELECT 'Foxtrot', 'management', 900 UNION
SELECT 'Hotel', 'other', 0; /*<<<<<<<<< ADDED for demo */
SELECT name, division, max(salary) AS msal FROM salaries GROUP BY division HAVING max(salary);
SELECT name, division, salary AS msal
FROM salaries AS s
WHERE salary = (
SELECT MAX(salary)
FROM salaries
WHERE division = s.division
);
例如，为另一个部门添加了一个薪水为0的行，那么第一个查询的结果是：
即other部门的组已被删除，因为最大薪水为false（0）。
而第二个查询的结果包括other部门：
英文:
> However, the use of the HAVING clause does not make much sense to me since it should simply filter out the groups with a false value for max(salary)
It would ONLY if the max(salary) per group was 0, according to:-
> If a HAVING clause is specified, it is evaluated once for each group of rows as a boolean expression. If the result of evaluating the HAVING clause is false, the group is discarded. If the HAVING clause is an aggregate expression, it is evaluated across all rows in the group. If a HAVING clause is a non-aggregate expression, it is evaluated with respect to an arbitrarily selected row from the group. The HAVING expression may refer to values, even aggregate functions, that are not in the result. https://www.sqlite.org/lang_select.html#generation_of_the_set_of_result_rows
So if you used, for example:-
CREATE TABLE salaries AS
SELECT 'Alpha' as name, 'computer' as division, 500 as salary UNION
SELECT 'Bravo', 'computer', 600 UNION
SELECT 'Charlie', 'accounting', 200 UNION
SELECT 'Delta', 'accounting', 300 UNION
SELECT 'Echo', 'management', 800 UNION
SELECT 'Foxtrot', 'management', 900 UNION
SELECT 'Hotel', 'other', 0; /*<<<<<<<<<< ADDED for demo */
SELECT name, division, max(salary) AS msal FROM salaries GROUP BY division HAVING max(salary);
SELECT name, division, salary AS msal
FROM salaries AS s
WHERE salary = (
SELECT MAX(salary)
FROM salaries
WHERE division = s.division
);
- i.e. another row with a 0 salary for another division then the result of the first query is:-
i.e. the group for the other division has been dropped as the max(salary) is false (0).
While the result of the second query includes the other division:-
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论