我正在嘗試在 select 陳述句中的子查詢中使用別名，如下所示：
SELECT
InvoiceId,
InvoiceTotal,
(SELECT
AVG(InvoiceTotal)
FROM Invoices) AS InvoiceAverage,
InvoiceTotal - (SELECT InvoiceAverage)
FROM Invoices;
當我嘗試執行查詢時，出現此錯誤：
'無效的列名'InvoiceAverage'。
uj5u.com熱心網友回復：
這里不需要子查詢，使用視窗聚合：
SELECT InvoiceId,
InvoiceTotal,
AVG(InvoiceTotal) OVER () AS InvoiceAverage,
InvoiceTotal - AVG(InvoiceTotal) OVER () AS YourAliasGoesHere
FROM dbo.Invoices;
uj5u.com熱心網友回復：
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/491961.html
下一篇：DB2：如何忽略例外？