Reference alias (calculated in SELECT) in WHERE clause

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

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

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.

165997 次浏览
小开
最佳答案

除了 ORDERBY 之外，您不能引用别名，因为 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;

我更喜欢后者。如果表达式非常复杂(或者计算代价高昂) ，那么您可能应该考虑使用计算列(或者持久化) ，特别是当大量查询引用同一个表达式时。

又及，你的恐惧似乎毫无根据。至少在这个简单的示例中，SQLServer 足够聪明，只执行一次计算，即使已经引用了两次。继续比较这些平面图，你会发现它们是一样的。如果你有一个更复杂的情况下，你看到表达式计算多次，请张贴更复杂的查询和计划。

下面是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;

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

enter image description here

小开

您可以使用 cross apply来完成这项工作

SELECT c.BalanceDue AS BalanceDue
FROM Invoices
cross apply (select (InvoiceTotal - PaymentTotal - CreditTotal) as BalanceDue) as c
WHERE  c.BalanceDue  > 0;
小开

实际上可以有效地定义一个既可以用于 SELECT、 WHERE 又可以用于其他子句的变量。

交叉连接不一定允许适当地绑定到被引用的表列，但是 OUTER APPY 允许-并且更透明地处理空值。

SELECT
vars.BalanceDue
FROM
Entity e
OUTER APPLY (
SELECT
-- variables
BalanceDue = e.EntityTypeId,
Variable2 = ...some..long..complex..expression..etc...
) vars
WHERE
vars.BalanceDue > 0

向 Syed Mehroz Alam致敬。

小开

作为强制在 WHERE 子句之前计算 SELECT 子句的解决方案，您可以将前者放在子查询中，而后者保留 在主要查询中:

SELECT * FROM (
SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Invoices) AS temp
WHERE BalanceDue > 0
小开

一个简单的方法

SELECT *  FROM Invoices GROUP BY id
HAVING(InvoiceTotal - PaymentTotal - CreditTotal) > 0

提交答案