为什么我不能在having子句中为聚合使用别名？

My code is like shown below :

我的代码如下所示：

select col1,count(col2) as col7
from --some join operation
group by col1
having col7 >= 3 -- replace col7 by count(col2) to make the code work

My code causes the error "Invalid column name 'col7' ". Why does this happen ? It seems illogical that SQL does not allow me to use col7 in the last line.

我的代码导致错误“无效的列名'col7'”。为什么会这样？ SQL不允许我在最后一行使用col7似乎不合逻辑。

I am using SQL server express 2008

我正在使用SQL server express 2008

7 个解决方案

#1

In MS SQL, the only place (I'm aware of) that you can reference aliases is in the ORDER BY clause. The ability to reference aliases in other parts of the query is a feature that many other db platforms have and honestly it annoys me that Microsoft hasn't considered it a useful enough feature to add it.

在MS SQL中，唯一可以引用别名的地方（我知道）在ORDER BY子句中。在查询的其他部分引用别名的能力是许多其他数据库平台所拥有的功能，老实说，它让我很恼火，微软并没有认为它是一个足够有用的功能来添加它。

#2

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

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

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.
9.TOP CLAUSE executed

1.首先形成FROM CLAUSE中所有表格的产品。 2.然后评估WHERE CLAUSE以消除不满足search_condition的行。 3.接下来，使用GROUP BY CLAUSE中的列对行进行分组。 4.然后，消除了不满足HAVING CLAUSE中的search_condition的组。 5.接下来，将评估SELECT CLAUSE目标列表中的表达式。 6.如果select子句中存在DISTINCT关键字，则现在会删除重复的行。 7.评估每个子选择后的UNION。 8.最后，生成的行根据ORDER BY CLAUSE中指定的列进行排序。 9.TOP CLAUSE已执行

Hope this answers your question!!!
Also it explains why the alias works in ORDER BY clause.

希望这能回答你的问题!!!它还解释了为什么别名在ORDER BY子句中起作用。

#3

Try with this one as the select list contains the same expression you can use in having clause also:

尝试使用此选项列表，因为选择列表包含您可以在having子句中使用的相同表达式：

SELECT COL1,COUNT(COL2) AS COL7
FROM --SOME JOIN OPERATION
GROUP BY COL1
HAVING COUNT(COL2) >= 3

#4

You should select twice to use the count() column

您应该选择两次以使用count（）列

select * from (select col1,count(col2) as col7
from --some join operation
group by col1) as temp
where temp.col7 >= 3

#5

U can use this code:

你可以使用这段代码：

IF OBJECT_ID('tempdb..#temp') is not null DROP TABLE #temp

-- Create tempurary table
CREATE TABLE #temp (Id BIGINT IDENTITY(1,1), col1 BIGINT, countOfcol2 BIGIN)

--insert from the table 2 #temp
INSERT INTO #temp (col1,countOfcol2) 

select col1,count(col2) as col7
from --some join operation

select col1,countOfcol2 from #temp
group by col1
having countOfcol2 >= 3 -- replace col7 by count(col2) to make the code work

#6

select col1,count(col2) as col7 from --some join operation group by col1 having count(col2) >= 3;

选择col1，count（col2）为col7，来自--some join操作组，col1有count（col2）> = 3;

Honestly I am miffed as to why SQL Server does not process the column alias. I use this as a workaround. It still prints the column name as your alias but processes using the original aggregate function.

老实说，我很不高兴为什么SQL Server不处理列别名。我用这个作为解决方法。它仍然将列名称打印为您的别名，但使用原始聚合函数进行处理。

#7

You can solve this by using a nested query.

您可以使用嵌套查询来解决此问题。

I have also run into this problem when I am wanting to improve performance. I have needed to run a count() based on certain fields within a JSON field in a table, obviously we would want to parse JSON only once instead of having to include a separate count in a where or have clause (especially an expensive one like in my case).

当我想提高性能时，我也遇到了这个问题。我需要根据表中JSON字段内的某些字段运行count（），显然我们只想解析JSON一次，而不必在where或has子句中包含一个单独的计数（特别是像在我的情况下）。

If col1 is a unique id, the most computationally efficient way could be to nest the count in a separate select

如果col1是唯一的id，那么计算效率最高的方法可能是将计数嵌套在单独的select中

select col1, innerquery.col7
from whatevertable
inner join (select col1, count(col2) as col7 
            from whatevertable 
            group by col1) as innerquery 
            on innerquery.col1 = whatevertable.col1
where innerquery.col7 >= 3;

This way the count is only ran once, creating a sort of temporary lookup table for reference by the rest of your query.

这样计数只运行一次，创建一种临时查找表供查询的其余部分参考。

Again, this only works if col1 is unique for every record, which normally isn't too much to ask since most tables have some sort of id primary key.

同样，这只适用于col1对于每条记录都是唯一的，由于大多数表都有某种id主键，因此通常不会太多。

#1

#2