别名在何处与具有之间的区别 [英] difference between where and having with respect to aliases

问题描述

如果我在select子句中创建别名，则不能在where子句中使用它，因为根据sql查询的执行顺序，where在select之前.

If I create an alias in the select clause then I cannot use it in the where clause because according to the order of execution of sql queries where comes before select.

但是我可以在select子句中创建一个别名，并在having子句中使用它，尽管having在select之前.

But I can create an alias in the select clause and use it in a having clause though having comes before select.

为什么会这样?

例如:

select type, (case when number>25 then 1 else 0 end) inc 
from animals
where inc='1';

这行不通.但是，

select type, (case when number>25 then 1 else 0 end) inc 
from animals
having inc='1'; 

这有效.为什么这样?

推荐答案

基本上是因为它们是出于不同目的而定义的. WHERE子句用于记录过滤，而HAVING子句用于通过聚合函数(GROUP BY)进行过滤. 在第二个查询中，正在使用隐式GROUP BY过滤，因此，例如，如果在SELECT子句中添加另一列，则会得到不同的结果.

Basically because they where defined for different purposes. The WHERE clause is for records filtering and the HAVING clause is designed for filtering with aggregate functions (GROUP BY). In your second query an implicit GROUP BY filtering is being used, so for instance, if you add another column to the SELECT clause you will end up with different results.

编辑(基于马丁·史密斯的修正)

EDIT based on correction by Martin Smith

HAVING是为了允许对GROUP BY产生的行进行过滤.如果未指定GROUP BY，则将整个结果视为一个组.

HAVING was created to allow filtering of rows resulting of a GROUP BY. When no GROUP BY is specified, the whole result is considered a group.

如果未指定<where clause>或<group by clause>， 然后让T为前一个<from clause>

If neither a <where clause> nor a <group by clause> is specified, then let T be the result of the preceding <from clause>

或

...该组是 整个表(如果未指定<group by clause>

...the group is the entire table if no <group by clause> is specified

编辑2 现在关于ALIAS:

EDIT 2 Now regarding the ALIAS:

有关搜索条件中列引用的WHERE子句的规范说:

The specification for the WHERE clause regarding the columns references in the search condition says this:

<search condition>中直接包含的每个<column reference> 明确引用T的列或作为外部引用.

Each <column reference> directly contained in the <search condition> shall unambiguously reference a column of T or be an outer reference.

请参阅:7.6 <where clause>，语法规则1.

Refer to: 7.6 <where clause>, Syntax Rule 1.

有关搜索条件中列引用的HAVING子句的规范说:

The specification for the HAVING clause regarding the columns references in the search condition says this:

直接包含在<search condition>中的每个<column reference>应该明确引用T的分组列 或作为外部参考.

Each <column reference> directly contained in the <search condition> shall unambiguously reference a grouping column of T or be an outer reference.

请参阅:7.8 <having clause>，语法规则1.

Refer to: 7.8 <having clause>, Syntax Rule 1.

分组列定义为:

<group by clause>中引用的列是分组列.

A column referenced in a <group by clause> is a grouping column.

因此，总而言之，WHERE必须引用表的一列，而HAVING子句必须引用该行组的一个分组列.

So in conclusion the WHERE must reference a column of the table and the HAVING clause must reference a grouping column of the group of rows.

(第二次非正式审核草案)ISO/IEC 9075:1992，数据库 语言SQL-1992年7月30日

(Second Informal Review Draft) ISO/IEC 9075:1992, Database Language SQL- July 30, 1992

这篇关于别名在何处与具有之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！