SQL 为什么我不能在 count(*) “列”中使用别名并在具有子句中引用它？

Why can't I use alias in a count(*) "column" and reference it in a having clause?

提问by André Pena

I was wondering why can't I use alias in a count(*) and reference it in the having clause. For instance:

我想知道为什么我不能在 count(*) 中使用别名并在 having 子句中引用它。例如：

select Store_id as StoreId, count(*) as _count
    from StoreProduct
    group by Store_id
        having _count > 0

Wouldn't work.. But it works if I remove _count and use count(*) instead.

行不通..但如果我删除 _count 并使用 count(*) 来代替它。

回答by martin clayton

See the document referencedby CodeByMoonlightin an answerto your recent question.

见引用文档通过CodeByMoonlight在回答对你最近的问题。

The HAVING clause is evaluated before the SELECT - so the server doesn't yet know about that alias.

HAVING 子句在 SELECT 之前进行评估 - 因此服务器还不知道该别名。

1. First the product of all tables in the fromclause is formed.
2. The whereclause is then evaluated to eliminate rows that do not satisfy the search_condition.
3. Next, the rows are grouped using the columns in the group byclause.
4. Then, Groups that do not satisfy the search_condition in the having clauseare eliminated.
5. Next, the expressions in the selectclause target list are evaluated.
6. If the distinctkeyword in present in the select clause, duplicate rows are now eliminated.
7. The unionis taken after each sub-select is evaluated.
8. Finally, the resulting rows are sorted according to the columns specified in the order byclause.

1. 首先形成from子句中所有表的乘积。
2. 在那里，然后条款进行评估，以消除不符合search_condition行。
3. 接下来，使用group by子句中的列对行进行分组。
4. 然后，将不满足having 子句中的 search_condition 的 Group剔除。
5. 接下来，将评估select子句目标列表中的表达式。
6. 如果select 子句中存在distinct关键字，现在将消除重复的行。
7. 在评估每个子选择之后采用联合。
8. 最后，结果行根据order by子句中指定的列进行排序。

回答by Shannon Severance

The selectclause is the last clause to be executed logically, except for order by. The havingclause happens before select, so the aliases are not available yet.

该select子句是逻辑上要执行的最后一个子句，除了order by. 该having子句发生在 select 之前，因此别名尚不可用。

If you really want to use an alias, not that I'd recommend doing this, an in-line view can be used to make the aliases available:

如果您真的想使用别名，而不是我建议这样做，则可以使用内嵌视图使别名可用：

select StoreId, _count
from (select Store_id as StoreId, count(*) as _count
    from StoreProduct
    group by Store_id) T
where _count > 0

Or in SQL Server 2005 and above, a CTE:

或者在 SQL Server 2005 及更高版本中，CTE：

; with T as (select Store_id as StoreId, count(*) as _count
    from StoreProduct
    group by Store_id)
select StoreId, _count
from T
where _count > 0

回答by Glenn Slaven

You can use the alias for count in the select clause, you just can't use it in the having statement, so this would work

你可以在 select 子句中使用 count 的别名，你只是不能在 have 语句中使用它，所以这会起作用

select Store_id as StoreId, count(*) as _count
    from StoreProduct
    group by Store_id
        having count(*) > 0

回答by Guffa

The aliases for the field names is only for naming the columns in the result, they can never be used inside the query. You can't do like this either:

字段名称的别名仅用于命名结果中的列，它们永远不能在查询中使用。你也不能这样做：

select Store_id as Asdf
from StoreProduct
where Asdf = 42

However, you can safely use count(*)in both places, and the database will recognise that it's the same value, so it won't be calculated twice.

但是，您可以count(*)在两个地方安全地使用，并且数据库会识别出它是相同的值，因此不会计算两次。

回答by rafaelvalle

In Hive 0.11.0 and later, columns can be specified by position if hive.groupby.orderby.position.alias is set to true.

在 Hive 0.11.0 及更高版本中，如果 hive.groupby.orderby.position.alias 设置为 true，则可以按位置指定列。

set hive.groupby.orderby.position.alias=true;
select Store_id as StoreId, count(*) as _count
from StoreProduct
group by 1

I'm don't understand the purpose of your query. Given the context of the query you posted, your condition is not necessary because items that do not exist, i. e. count 0, will never be a result from a query...

我不明白您查询的目的。鉴于您发布的查询的上下文，您的条件不是必需的，因为不存在的项目，即计数 0，永远不会是查询的结果......

回答by New Dawg Learning Old Tricks

Here is my contribution (based on the code posted here):

这是我的贡献（基于此处发布的代码）：

select * from (
  SELECT Store_id as StoreId, Count(*) as StoreCount 
  FROM StoreProduct
  group by Store_id
  ) data
where data.StoreCount > 0

回答by Jose Chama

You can use the alias for the aggregates in SQL, but that is just to show the alias in the results headers. But when you want to have a condition with the aggregate function in the having you still need to use the aggregate because it evaluates the function and not the name.

您可以在 SQL 中为聚合使用别名，但这只是为了在结果标题中显示别名。但是，当您希望在具有聚合函数的条件下，您仍然需要使用聚合，因为它评估的是函数而不是名称。

回答by Jimmy

Probably because that's the way sql defines the namespaces. take, for example:

可能是因为这是 sql 定义命名空间的方式。举个例子：

  select a as b, b as a
    from table
   where b = '5'
order by a

what do a and b refer to? The designers just chose to make the aliases only appear on the "outside" of the query.

a 和 b 指的是什么？设计者只是选择让别名只出现在查询的“外部”。