sql-server - 在表达式中使用别名的选择语句

Question

有没有办法在另一个表达式中使用一个表达式？我想根据收入（扩展）计算利润 - 商品成本（LMOZ）

寻找这样的东西：SUM("Extension") - SUM("LMOZ") AS 利润。

示例查询：

Select distinct Top 10 rtrim(customer) as SaleCust,Sum(Extension) As Extension,
SUM(COST_Material) + SUM(Cost_Labor) + SUM(Cost_Outside) + SUM(Cost_Overhead) + SUM(Cost_Other) AS LMOZ 
From V_Order_Hist_Line 
Where Year(Date_Invoice) = 2018 AND Salesperson = 'DJW' AND PRODUCT_LINE LIKE 'R_' 
Group By rtrim(customer)
ORDER BY Extension desc

标签： sql-serverpervasive-sql

Answer 1

您可以使用子查询，但 order by 在子查询中不起作用。您可以执行以下操作。

SELECT distinct Top 10 SaleCust, (Extension -  LMOZ) AS Calc, OrdExtension
FROM (
Select  rtrim(customer) as SaleCust,Sum(Extension) As Extension,
SUM(COST_Material) + SUM(Cost_Labor) + SUM(Cost_Outside) + SUM(Cost_Overhead) + SUM(Cost_Other) AS LMOZ,
ROW_NUMBER() OVER(ORDER BY Extension DESC) AS OrdExtension
From V_Order_Hist_Line 
Where Year(Date_Invoice) = 2018 AND Salesperson = 'DJW' AND PRODUCT_LINE LIKE 'R_' 
Group By rtrim(customer)
)
ORDER BY OrdExtension desc