sql - 无效的列名 'FinalTotal'

Question

SELECT 
    o.OrderID, ProductName, OrderDate, ItemPrice, TaxAmount, 
    DiscountAmount, DiscountAmount - ItemPrice AS FinalTotal, 
    Quantity, ShipDate,FinalTotal * Quantity AS ItemTotal
FROM
    Orders AS o 
JOIN 
    OrderItems AS oi ON o.OrderID = oi.OrderID
JOIN 
    Products AS p ON oi.ProductID = p.ProductID;

这是我的代码，它一直显示列名称“FinalTotal”无效。我在这里做错了什么？

Answer 1

您不能像那样在同一查询级别上使用列别名。

在ItemTotal的计算中使用FinalToal的完整表达式

SELECT o.OrderID, ProductName, OrderDate, ItemPrice, TaxAmount, DiscountAmount,
       DiscountAmount - ItemPrice AS FinalTotal, 
       Quantity, ShipDate,
       (DiscountAmount - ItemPrice) * Quantity AS ItemTotal
FROM Orders AS o JOIN OrderItems AS oi
    ON o.OrderID = oi.OrderID
 JOIN Products AS p
    ON oi.ProductID = p.ProductID;

或者是使用派生查询或 CTE