英文:
How is the having clause creating the correct output in this example?
问题
以下是要翻译的内容：
在SQLite中，HAVING子句的工作原理是什么？我的代码可以正常工作，并返回每个部门中薪水最高的人的姓名和部门。然而，我不明白为什么要使用HAVING子句，因为它应该只是筛选具有max(salary)为false值的分组：
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);
为什么上述查询产生与此查询相同的输出：
SELECT name, division
FROM salaries AS s
WHERE salary = (
SELECT MAX(salary)
FROM salaries
WHERE division = s.division
);
英文:
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”部门的组已被删除，因为max(salary)为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:-
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论