使用HAVING与子查询有什么区别 [英] What is the difference of using HAVING vs a subquery

问题描述

我是SQL的新手，正在通过数据营学习.我想知道您是否可以通过"HAVING"获得与嵌套"WHERE"子句相同的结果.

I am new to SQL and doing the learning via datacamp. I was wondering if you can achieve the same result with 'HAVING' as with a nested 'WHERE' clause.

相关: SQL-具有VS where 我了解HAVING与诸如min，max，..

Related: SQL - having VS where I understand that HAVING is used with aggregate functions such as min, max, ..

如何用HAVING重写以下内容?:

How could I rewrite the following with HAVING?:

SELECT *
FROM populations
WHERE year = 2015 AND life_expectancy >(
   SELECT AVG(life_expectancy)*1.15
   FROM populations
);

假设我在人口"表中有6列:A(字符)，B(字符)，C(字符)，D(数字，即示例中的life_expectancy)，年份和E(数字).

Suppose I have 6 columns in the table 'populations': A (character), B (character), C (Character), D (number, i.e. life_expectancy as in the example), year and E (number).

我尝试了以下操作:

SELECT *
FROM populations
WHERE year = 2015
GROUP BY A, B, C, year
HAVING life_expectancy > AVG(life_expectancy)*1.15; 

但是，这将返回一个空表.我意识到这不是一个可复制的示例，但也许足以解释为什么它可能无法正常工作:)

However, this returns an empty table. I realise it's not a reproducible example, but perhaps a general explanation as to why it might not be working would suffice :)

推荐答案

HAVING通常用于限制GROUP BY子句的结果，就像使用WHERE来限制GROUP BY子句的结果一样SELECT子句.

HAVING is generally used to limit the results of a GROUP BY clause, in the same way that a WHERE is used to limit the results of a SELECT clause.

此外，虽然WHERE子句不能直接包含聚合，但是您可以使用HAVING子句(带有聚合)，也可以在包含聚合的WHERE子句中使用子查询

In addition, while a WHERE clause cannot contain aggregates directly, you can either use a HAVING clause (with the aggregates), or you could use a sub-query in the WHERE clause which contains the aggregates.

除非您要对数据进行分组或以其他方式进行汇总，否则我看不到为什么要将WHERE子句重写为HAVING子句.

Unless you are grouping your data, or otherwise aggregating it, then I cannot see why you would want to rewrite your WHERE clause as a HAVING clause.

在您的示例中，您正在基于A，B，C和Year创建一组组.然后，将这些组限制为该组的预期寿命(该寿命意味着什么?)大于该相同组的平均预期寿命乘以1.15的组.这没有任何意义，这是您已经建立的.

In your example, you are creating a set of groups, based on A, B, C and Year. Then, you are limiting those groups to the ones where the life-expectancy of the group (whatever this is meant to be?) is greater than the average life-expectancy of that same group multiplied by 1.15. It doesn't make any sense, which is what you had already established.

这篇关于使用HAVING与子查询有什么区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！