如何減去兩個別名列？

我需要找到具有別名的兩列之間的區別。

我得到的錯誤是：“表達式錯誤：Highest_Cost - Lowest_Cost”無效的列名：Highest_Cost

這是我的查詢：

select part,min(cost) as Lowest_Cost,max(cost) as Highest_Cost, min(date_received) as First_date, max(date_received) as Last_Date, description, Highest_Cost - Lowest_Cost as Difference
from v_po_history 
where part not like '*%' and date_received >= '2022-04-01' and date_received <= '2022-04-30' 
and location = 'HS'
group by part,description

你需要做這樣的事情：

SELECT
   x.*,
   x.Highest_Cost - Lowest_Cost AS Difference
FROM 
(
   SELECT
       part,
       MIN(cost) AS Lowest_Cost,
       MAX(cost) AS Highest_Cost,
       MIN(date_received) AS First_date,
       MAX(date_received) AS Last_Date,
       description
   FROM v_po_history
   WHERE
       part NOT LIKE '*%'
       AND date_received >= '2022-04-01'
       AND date_received <= '2022-04-30'
       AND location = 'HS'
   GROUP BY
       part,
       description
) AS x;

對於 SQL 查詢，您編寫查詢的方式與處理查詢的方式不同。

在您的範例中，選擇列表中的列是最後投影的，因此當您嘗試在選擇列表中再次引用別名時，別名不會綁定到表達式。

通過將初始查詢粘貼在派生表中，您可以從派生表中進行選擇並引用別名，因為您是從該初始結果中進行選擇的。

Google搜尋一個小時後，我找不到答案。我發布了這個問題和我的下一個Google搜尋，我找到了答案。基本上你必須再次執行計算。您不能在聲明它們的 select 語句中訪問別名列。

select part,min(cost) as Lowest_Cost,max(cost) as Highest_Cost, min(date_received) as First_date, max(date_received) as Last_Date, description, (max(cost) - min(cost)) as Difference
from v_po_history 
where part not like '*%' and date_received >= '2022-04-01' and date_received <= '2022-04-30' and location = 'HS'
group by part,description

引用自：https://dba.stackexchange.com/questions/311908