英文:
Invalid column name with alias and HAVING
问题
我添加了一些列并给它们起了别名。然后我想只显示那些加法结果大于某个特定值的行：
SELECT (COALESCE(col1, 0.0) + COALESCE(col, 0.0)) as total, id, email
FROM table
HAVING total > 20
但是我得到了“无效的列名 'total'”。
我以为可以同时使用别名和 HAVING，那么为什么会报错无效呢？
英文:
I am adding a few columns and giving them alias. Then I want to display only the rows where the result of the addition is greater than certain amount:
SELECT (COALESCE(col1, 0.0) + COALESCE(col, 0.0)) as total, id, email
FROM table
HAVING total > 20
but I get Invalid column name 'total'.
I thought I can use alias and HAVING together, so why is it invalid?
答案1
得分: 2
你不真的需要为此使用子查询，你可以在where子句中简单重复表达式。
如果重复表达式令人讨厌，你可以使用apply并重复使用它。
你提到了加法，但在你的示例查询中没有有效的加法（也没有聚合），不过如果有的话，同样适用，也许以下是你的意图？
从表t
cross apply(values(COALESCE(col1, 0) + COALESCE(col, 0)))tot(Total)
where tot.Total > 20;
英文:
You don't really need a sub query for this, you can simply repeat the expression in the where clause.
If repeating the expression is abhorrent you can apply it and re-use it.
You mention addition but there is no valid addition in your example query (nor aggregation), although if there were the same applies, perhaps the following was your intention?
SELECT tot.total, id, email
FROM table t
cross apply(values(COALESCE(col1, 0) + COALESCE(col, 0)))tot(Total)
where tot.Total > 20;
答案2
得分: 1
你可以像这样简单地将这两列相加：
SELECT COALESCE(col1, 0.0) + COALESCE(col, 0.0) AS total, id, email
FROM table
WHERE COALESCE(col1, 0.0) + COALESCE(col, 0.0) > 20
聚合（因此也包括 HAVING 子句）仅在你将不同行的值相加时才需要。
请注意，SELECT 部分实际上是在 WHERE 部分之后执行的。因此，WHERE 部分不知道 SELECT 部分中的别名，你必须重复表达式。
你也可以使用子查询来计算总和，但对于这个简单的情况来说可能会显得太复杂，而且可能会减慢查询速度。
英文:
You can simply add the two columns like this:
SELECT COALESCE(col1, 0.0) + COALESCE(col, 0.0) AS total, id, email
FROM table
WHERE COALESCE(col1, 0.0) + COALESCE(col, 0.0) > 20
Aggregation (and therefore also the HAVING clause) is only required if you are adding values of different rows.
Note that the SELECT part is actually executed after the WHERE part. So, the WHERE part does not know the aliases given in the SELECT part and you must repeat the expression.
You could also use a sub-query to calculate the sum instead, but this seems to complicated for this simple case and can potentially slow down the query.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论