帶有 JOIN 和 GROUP BY 查詢的 SELECT 中出現錯誤“列不存在”

我將 PostgreSQL 9.1 與 Ruby on Rails 應用程序一起使用。

我正在嘗試列出屬於同一項目 ID (proj_sous_projet_id = 2) 的每個“費用”的最後一個版本（在我的歷史表中：hist_version_charges）。

這使我使用 max() 聚合函式並將結果應用於同一表上的 JOIN 函式，因為如果 SELECT 子句中的列沒有出現在 GROUP BY 子句中，PostgreSQL 不會授權使用它們，儘管使用最大值() 顯然我對包含最大值的行感興趣！

這是我的查詢：

SELECT h_v_charges.*, 
      max(last_v.version) as lv 
FROM hist_versions_charges h_v_charges 
   JOIN hist_versions_charges last_v 
     ON h_v_charges.version = lv 
   AND h_v_charges.proj_charge_id = last_v.proj_charge_id 
GROUP BY last_v.proj_sous_projet_id, 
        last_v.proj_charge_id 
HAVING last_v.proj_sous_projet_id = 2 
ORDER BY h_v_charges.proj_charge_id ASC;

我得到的錯誤資訊：

ERROR:  column "lv" does not exist
LINE 1: ..._versions_charges last_v ON h_v_charges.version = lv AND h_v...
                                                            ^
********** Error **********

ERROR: column "lv" does not exist
SQL state: 42703
Character: 147

我也嘗試使用“last_v.lv”，但錯誤仍然相同。

如果有人知道出了什麼問題，她非常受歡迎。

=== 更新 ===

根據 * a_horse_with_no_name * 和Colin ’t Hart 的回答，我最終得到了以下查詢：

SELECT *
FROM (
   SELECT *, max(version) OVER (PARTITION BY proj_charge_id) AS lv
   FROM hist_versions_charges
   WHERE proj_sous_projet_id = 2) AS hv
WHERE hv.lv = hv.version
ORDER BY hv.proj_charge_id ASC;

使用單個 ORDER BY 會稍微快一些。

我也嘗試了帶有 WITH 子句的查詢。雖然“更好”，但它會產生額外的處理費用。據我所知，將來我不會在同一個主查詢中重複使用子查詢兩次或更多次，我可以使用簡單的子查詢。

無論如何感謝 a_horse_with_no_name 和Colin ’t Hart。我學到了很多東西！

你可能想要這樣的東西：

SELECT h_v_charges.*, 
      last_v.last_version
FROM hist_versions_charges h_v_charges 
 JOIN (select proj_charge_id, 
              max(version) as last_version
       from hist_versions_charges 
       where proj_sous_projet_id = 2  
       group by proj_charge_id
 ) last_v  
 ON h_v_charges.version = last_v.last_version
AND h_v_charges.proj_charge_id = last_v.proj_charge_id 
ORDER BY h_v_charges.proj_charge_id ASC;

一個可能（因為不需要加入）更快的解決方案是：

select *
from (
  select hvc.*, 
         row_number() over (partition by proj_charge_id order by version desc) as rn
  from hist_versions_charges as hvc
  where proj_sous_projet_id = 2  
) as hv
where rn = 1
order by hv.proj_charge_id ASC;

正如 Colin 所指出的，這也可以寫成：

with hv as (
 select hvc.*, 
        row_number() over (partition by proj_charge_id order by version desc) as rn
 from hist_versions_charges as hvc
 where proj_sous_projet_id = 2  
) 
select *
from hv
where rn = 1
order by hv.proj_charge_id ASC;

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