WHERE 子句中的引用别名(在 SELECT 中计算)

问题描述

SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Invoices
WHERE BalanceDue > 0 --error

在所选列的列表中设置为变量的计算值BalanceDue不能在WHERE子句中使用.

The calculated value BalanceDue that is set as a variable in the list of selected columns cannot be used in the WHERE clause.

有办法吗?在这个相关问题中(在 Where 子句中使用 MySQL Select 语句中的变量)，答案似乎是，实际上，不，您只需写出计算(并在查询中执行该计算)两次，但没有一个是令人满意的.

Is there a way that it can? In this related question (Using a variable in MySQL Select Statment in a Where Clause), it seems like the answer would be, actually, no, you would just write out the calculation (and perform that calculation in the query) twice, none of which is satisfactory.

推荐答案

除了 ORDER BY 之外，您不能引用别名，因为 SELECT 是被评估的倒数第二个子句.两种解决方法:

You can't reference an alias except in ORDER BY because SELECT is the second last clause that's evaluated. Two workarounds:

SELECT BalanceDue FROM (
  SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
  FROM Invoices
) AS x
WHERE BalanceDue > 0;

或者只是重复表达:

SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Invoices
WHERE  (InvoiceTotal - PaymentTotal - CreditTotal)  > 0;

我更喜欢后者.如果表达式极其复杂(或计算成本高)，您可能应该考虑使用计算列(并且可能是持久化的)，尤其是当大量查询引用同一个表达式时.

I prefer the latter. If the expression is extremely complex (or costly to calculate) you should probably consider a computed column (and perhaps persisted) instead, especially if a lot of queries refer to this same expression.

PS 你的担心似乎没有根据.至少在这个简单的示例中，SQL Server 足够智能，即使您已经引用了两次，也只能执行一次计算.继续比较计划；你会看到它们是相同的.如果您有更复杂的情况，您看到表达式被多次计算，请发布更复杂的查询和计划.

PS your fears seem unfounded. In this simple example at least, SQL Server is smart enough to only perform the calculation once, even though you've referenced it twice. Go ahead and compare the plans; you'll see they're identical. If you have a more complex case where you see the expression evaluated multiple times, please post the more complex query and the plans.

以下是 5 个示例查询，它们都产生完全相同的执行计划:

Here are 5 example queries that all yield the exact same execution plan:

SELECT LEN(name) + column_id AS x
FROM sys.all_columns
WHERE LEN(name) + column_id > 30;

SELECT x FROM (
SELECT LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE x > 30;

SELECT LEN(name) + column_id AS x
FROM sys.all_columns
WHERE column_id + LEN(name) > 30;

SELECT name, column_id, x FROM (
SELECT name, column_id, LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE x > 30;

SELECT name, column_id, x FROM (
SELECT name, column_id, LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE LEN(name) + column_id > 30;

所有五个查询的结果计划:

Resulting plan for all five queries:

这篇关于WHERE 子句中的引用别名(在 SELECT 中计算)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！