为什么我不能在计数(*)“列"中使用别名?并在具有条款中引用它?

2021-11-26 00:00:00 sql alias sql-server

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

选择Store_id作为StoreId，count(*)作为_count从商店产品按 Store_id 分组_count >0

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

解决方案

参见 CodeByMoonlight 在 文档2068439/why-cant-i-apply-a-criteria-on-a-sub-query/2068492#2068492">回答 你最近的问题.

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

<块引用>

  1. 首先形成 from 子句中所有表的乘积.
  2. 然后对 where 子句求值以消除不满足的行search_condition.
  3. 接下来，使用 group by 子句中的列对行进行分组.
  4. 那么，不满足中的search_condition的组有子句被删除.
  5. 接下来，select 子句目标列表中的表达式是评估.
  6. 如果distinct关键字出现在select子句中，重复行现已淘汰.
  7. 在评估每个子选择后采用 联合.
  8. 最后，根据列对结果行进行排序在 order by 子句中指定.

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

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.

解决方案

See the document referenced by CodeByMoonlight in an answer to your recent question.

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

  1. First the product of all tables in the from clause is formed.
  2. The where clause 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 by clause.
  4. Then, Groups that do not satisfy the search_condition in the having clause are eliminated.
  5. Next, the expressions in the select clause target list are evaluated.
  6. If the distinct keyword in present in the select clause, duplicate rows are now eliminated.
  7. The union is taken after each sub-select is evaluated.
  8. Finally, the resulting rows are sorted according to the columns specified in the order by clause.

相关文章