SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Invoices
WHERE BalanceDue > 0 --error
在所选列的列表中设置为变量的计算值 BalanceDue
不能在 WHERE
子句中使用。有办法吗?在这个相关问题 (Using a variable in MySQL Select Statment in a Where Clause) 中,答案似乎是,实际上,不,您只需写出计算(并在查询中执行该计算)两次,但没有一个是令人满意的。
最佳答案
除了 ORDER BY 之外,您不能引用别名,因为 SELECT 是被评估的倒数第二个子句。两种解决方法:
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;
我更喜欢后者。如果表达式极其复杂(或计算成本高),您可能应该考虑使用计算列(并且可能是持久化的),尤其是在大量查询引用同一个表达式的情况下。
PS你的恐惧似乎没有根据。至少在这个简单的示例中,SQL Server 足够智能,即使您已经引用了两次,也只能执行一次计算。继续比较计划;你会看到它们是相同的。如果您有更复杂的情况,您看到表达式被多次计算,请发布更复杂的查询和计划。
以下是 5 个示例查询,它们都产生完全相同的执行计划:
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;
所有五个查询的结果计划:
关于sql - WHERE 子句中的引用别名(在 SELECT 中计算),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/11182339/