SQL 为什么我不能在 have 子句中为聚合使用别名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14048672/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why can't I use an alias for an aggregate in a having clause?
提问by sky scraper
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
采纳答案by Brandon Moore
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 子句中。在查询的其他部分引用别名的能力是许多其他数据库平台都拥有的功能，老实说，微软认为它不是一个足够有用的功能来添加它让我很恼火。
回答by krishna
The HAVINGclause is evaluated before the SELECT- so the server doesn't yet know about that alias.
该HAVING子句在SELECT-之前进行评估，因此服务器尚不知道该别名。
First, the product of all tables in the
FROMclause is formed.The
WHEREclause is then evaluated to eliminate rows that do not satisfy the search_condition.Next, the rows are grouped using the columns in the
GROUP BYclause.Then, groups that do not satisfy the
search_conditionin theHAVINGclause are eliminated.Next, the expressions in the
SELECTstatement target list are evaluated.If the
DISTINCTkeyword in present in the select clause, duplicate rows are now eliminated.The
UNIONis taken after each sub-select is evaluated.Finally, the resulting rows are sorted according to the columns specified in the
ORDER BYclause.TOPclause is executed.
首先，
FROM形成子句中所有表的乘积。WHERE然后评估该子句以消除不满足 search_condition 的行。接下来，使用
GROUP BY子句中的列对行进行分组。随后，不满足的组
search_condition中HAVING条款被删除。接下来，
SELECT评估语句目标列表中的表达式。如果
DISTINCT关键字 in 出现在 select 子句中，现在会消除重复的行。在
UNION评估每个子选择之后采用。最后，结果行根据
ORDER BY子句中指定的列进行排序。TOP条款被执行。
Hope this answers your question. Also, it explains why the alias works in ORDER BYclause.
希望这能回答你的问题。此外，它还解释了别名在ORDER BY子句中起作用的原因。
回答by Pratik
Try with this one as the select list contains the same expression you can use in having clause also:
尝试使用这个，因为选择列表包含您也可以在 have 子句中使用的相同表达式：
SELECT COL1,COUNT(COL2) AS COL7
FROM --SOME JOIN OPERATION
GROUP BY COL1
HAVING COUNT(COL2) >= 3
回答by Junnan Wang
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
回答by ali khezri
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
回答by Broper
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 whereor haveclause (especially an expensive one like in my case).
当我想提高性能时，我也遇到了这个问题。我需要count()基于表中 JSON 字段中的某些字段运行一个，显然我们只想解析 JSON 一次，而不必在whereorhave子句中包含单独的计数（尤其是像我这样的昂贵的）。
If col1is 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 col1is unique for every record, which normally isn't too much to ask since most tables have some sort of id primary key.
同样，这仅适用col1于每条记录都是唯一的，这通常不会问太多，因为大多数表都有某种 id 主键。
回答by Pearl Puri
select col1,count(col2) as col7 from --some join operation group by col1 having count(col2)>= 3;
select col1,count(col2) as col7 from --some join operation group by col1 with 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 不处理列别名的原因感到恼火。我用它作为一种解决方法。它仍然将列名打印为您的别名，但使用原始聚合函数进行处理。
