在 WHERE 语句中使用别名列 [英] Using Alias Column in WHERE Statement

问题描述

我知道我不能在 WHERE 子句中使用别名列，所以我试图找出编写查询的最佳方式，但我很挣扎.我尝试了HAVING"来过滤它，但是我不能同时使用 GROUP BY，这使得我的 Subtotal 列对整个数据库中的所有记录求和.

I know that I cannot use an alias column inside of a WHERE clause, so I'm trying to figure out the best way to write my query, but am struggling. I tried "HAVING" to filter it, but then I can't use GROUP BY at the same time, which makes my Subtotal column sum all of the records in the entire DB.

这是我想要做的:

SELECT a.ID, SUM(b.Qty * b.Price) AS Subtotal
FROM tbl_One AS a
LEFT JOIN tbl_Two AS b ON b.TwoID = a.ID
WHERE Subtotal > 100 AND Subtotal < 200
GROUP BY a.ID

任何有关我如何以不同方式执行此操作的建议将不胜感激！

Any suggestions on how I could do this differently would be greatly appreciated!

推荐答案

你说得对，你不能在 WHERE 或 HAVING 子句中调用你的别名，但除此之外我不确定你的意思；GROUP BY 和 HAVING 应该一起使用:

You're right, you cannot call out your alias in the WHERE or HAVING clauses, but other than that I'm not sure what you mean; GROUP BY and HAVING are meant to be used together:

SELECT a.ID, sum(b.Qty*b.Price) AS Subtotal
FROM tbl_One AS a
LEFT JOIN tbl_Two AS b ON b.TwoID = a.ID
GROUP BY a.ID
HAVING sum(b.Qty*b.Price) > 100 AND sum(b.Qty*b.Price) < 200

这篇关于在 WHERE 语句中使用别名列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！