无法在SQL内部的子查询中使用别名

Question

我正在尝试在选择语句中的子查询中使用一个类似的别名，如下所示：

SELECT 
     InvoiceId,
     InvoiceTotal,
     (SELECT 
       AVG(InvoiceTotal)
       FROM Invoices) AS InvoiceAverage,
     InvoiceTotal - (SELECT InvoiceAverage)
FROM Invoices;

当我尝试执行查询时，我会收到此错误：

“无效列”名称“ InvoICeavering”。

Answer 1

这里无需在此处使用子查询，请使用窗口的聚合物：

SELECT InvoiceId,
       InvoiceTotal,
       AVG(InvoiceTotal) OVER () AS InvoiceAverage,
       InvoiceTotal - AVG(InvoiceTotal) OVER () AS YourAliasGoesHere
FROM dbo.Invoices;

Answer 2

您可以直接从InvoIcetotal AS直接减去子查询值

SELECT 
     InvoiceId,
     InvoiceTotal,
     (SELECT AVG(InvoiceTotal) FROM Invoices) AS InvoiceAverage,
     (InvoiceTotal - (SELECT AVG(InvoiceTotal) FROM Invoices)) AS InvoiceCalculatedValue
FROM Invoices;

，也可以将AVG值存储在变量中并在选择查询中使用

DECLARE @InvoiceAverage AS DECIMAL (18,2) = 0;
SELECT @InvoiceAverage = AVG(InvoiceTotal) FROM Invoices;

SELECT 
     InvoiceId,
     InvoiceTotal,
     @InvoiceAverage AS InvoiceAverage
     (InvoiceTotal - @InvoiceAverage) AS InvoiceCalculatedValue
FROM Invoices;