如何在 HAVING 子句中重用计算列? [英] How to reuse calculated column in HAVING clause?

问题描述

请看下面的查询

SELECT 
    product_id, SUM(unit_price * quantity) Total
FROM 
    tblOrders
GROUP BY 
    product_id
HAVING
    SUM(unit_price * quantity) > 1000

上述查询工作正常.但是你可以在那里的查询中看到，Total 是一个计算列，并且在 HAVING 子句中再次使用了相同的计算.

The above query works fine. But you can see in there query, Total is a calculated column, and the same calculation is used again in the HAVING clause.

如何避免在 HAVING 子句中使用相同的计算?

How can I avoid using this same calculation in the HAVING clause?

推荐答案

好吧，在过去几天对这个主题进行了更多研究之后，我找到了我的答案.事实上，我在问题中发布的查询是完全正确的，只是我们可以用其他方式编写该查询，但我们无法避免实际重用计算，即使我们将查询 嵌套 如下:

Well, after doing some more study in this subject last couple of days, i have found my answer. In fact, my query what i posted in the question is completely correct, just we can write that query in other way(s), but we can't avoid to reuse the calculation actually, even though we make the query nested like below :

SELECT *
FROM
(
    SELECT product_id,
           SUM(unit_price * quantity) Total
    FROM tblOrders
    GROUP BY product_id
) A
WHERE A.Total > 1000;

这只是因为SELECT语句的逻辑处理顺序.看看下面的顺序:

This is just because the order of logical processing of the SELECT statement. Look at the below order :

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

注意SELECT语句的顺序，SELECT语句中定义的任何列别名或派生列都不能被前面顺序小于8的子句引用.你可以得到关于它的更多信息 这里

Notice the order of SELECT statement, any column aliases or derived columns defined in SELECT statement cannot be referenced by preceding clauses whose orders are less than 8. You can get more information about it Here

这篇关于如何在 HAVING 子句中重用计算列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！