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

Question

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

寻找类似这样的东西：SUM("扩展"( - SUM("LMOZ"( 作为利润。

示例查询：

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

Answer 1

是的，使用子查询获取范围内的列别名或再次重复所有内容。

子查询模板：

SELECT *, Extension - LMOZ AS Profit
FROM ( ... your query ... ) alias

包含在您的查询中：

SELECT *, Extension - LMOZ AS Profit
FROM (
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
) t

Answer 2

您可以使用子查询，但排序依据在子查询中不起作用。您可以执行以下操作。

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