在 SQL 中，HAVING 是在 SELECT 之前还是之后处理? [英] In SQL, is HAVING processed before or after SELECT?

问题描述

我惊讶地发现以下方法有效:

I was surprised to find that the following works:

    SELECT name, height * weight as inchpounds
    FROM sashelp.class 
    HAVING inchpounds > 5000;

因为我认为 HAVING 子句是在 SELECT 子句之前严格处理的.这实际上是我的错误假设吗?或者这是SAS对SQL的实现所特有的?

Since I thought the HAVING clause was processed strictly before the SELECT clause. Is this in fact a false assumption on my part? Or is this peculiar to SAS's implementation of SQL?

推荐答案

严格来说，如果我没记错的话，SQL 标准定义了 HAVING 子句要在 SELECT 子句.因此，在 SELECT 中定义的别名不能在 HAVING 子句中使用.

Strictly speaking, and if I remember well, the SQL standard define HAVING clause to be processed before the SELECT clause. So, an alias defined in the SELECT, cannot be used in HAVING clause.

在相关问题中查看我的回答:在where子句中使用'case expression column'作为处理SELECT语句的顺序，即:

See my answer in a related question: Using 'case expression column' in where clause for the order of proccessing a SELECT statement, which is:

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY 

因此，在大多数 SQL 版本中，该查询将产生错误.MySQL 是我所知道的一个例外，它允许这种与标准的偏差(并且还允许在 GROUP BY 子句中使用 SELECT 别名).

So, in most SQL incarnations, that query will yield an error. MySQL is one exception I know that allows this deviation from the standard (and where SELECT aliases are allowed to be used in the GROUP BY clause as well).

正如@a_horse_with_no_name 在评论中提到的，查询在大多数 SQL 产品中也会由于另一个原因而失败:HAVING 没有 GROUP BY 意味着有一个聚合所有的行，因此在 HAVING 和 SELECT 子句中只允许使用聚合函数.

As @a_horse_with_no_name mentioned in comments, the query would fail in most SQL products for another reason, too: HAVING without GROUP BY means there is an aggregation over all the rows and thus only aggregate functions would be allowed in both the HAVING and the SELECT clauses.

在查看 SAS SQL 文档和示例后，您上面发布的查询似乎在 SAS SQL 中有效并按此方式执行(在标准 SQL 中):

After looking at the SAS SQL documentation and examples, it seems the query you posted above is valid in SAS SQL and executed as this (in standard SQL):

SELECT name, height * weight AS inchpounds
FROM sashelp.class 
WHERE height * weight > 5000;

如果我理解正确，您还可以将聚合函数与非聚合列混合使用，如下所示:

If I understand it correctly, you may also mix aggregate functions with non-aggregated columns, like this:

SELECT name, height, 
       MAX(height) AS max_height
FROM sashelp.class ;

将被评估为(在标准 SQL 中):

which would be evaluated as (in standard SQL):

SELECT name, height, 
       MAX(height) OVER () AS max_height
FROM sashelp.class ;

这篇关于在 SQL 中，HAVING 是在 SELECT 之前还是之后处理?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！