使用Alias列计算另一个Alias列

Question

我有以下SQL语句：

SELECT SomeData.., Order.EurAmount AS OrderTotalEeur, 
--Begin Subselect
(SELECT Sum(Invoice.EurAmount) 
FROM Invoice WHERE Invoice.OrderId = Order.Id 
GROUP BY Invoice.OrderId) As InvoiceTotalEur
--End Subselect
([Order].EurAmount - IIF(InvoiceTotalEur is null, 0, InvoiceTotalEur) AS Diff
FROM [Order]

正如你所看到的，我想得到两个金额(OrderTotalEur和InvoiceTotalEur)，没什么大不了的。但是，我也想使用这两个别名来计算另一列( diff )，因为它不识别用于计算diff列的两个别名。

我能做的是重复Subselect两次(对于iif和值)，但是这不可能是正确的方法，对吗？

Answer 1

可以将查询添加为子查询：

  SELECT SomeData..,  
    OrderTotalEeur,
    InvoiceTotalEur,
    (OrderTotalEeur - IIF(InvoiceTotalEur is null, 0, InvoiceTotalEur) AS Diff
  FROM (
      SELECT SomeData.., Order.EurAmount AS OrderTotalEeur, 
      --Begin Subselect
      (SELECT Sum(Invoice.EurAmount) 
      FROM Invoice WHERE Invoice.OrderId = Order.Id 
       GROUP BY Invoice.OrderId) As InvoiceTotalEur
      --End Subselect
    FROM [Order]
    ) myquery

然后，在上层，您可以访问别名。