如何通過外部查詢屬性限制內部查詢

October 4, 2016

我需要按多個條件對錶進行排序，選擇第一個並與另一個表關聯。數據庫是 Postgres。
假設我們有幾個球隊的球員在哪裡踢球players。teams這不是模型的真實組成，但對於我們的範例應該足夠了。
team要求是獲取播放的最新記錄，如果存在player則按順序排列，否則按.teams.end_date``teams.start_date
該算法似乎是players.id按上述標準分組的，並按上述標準排序。但是，我所有將結果限制為 1 的嘗試都給了我team按該標準排序的第一條記錄。
這是我認為最接近的：
SELECT 
 latest_teams.* 
FROM players
 JOIN (
   SELECT 
     player_id,
     team_name,
     start_date,
     end_date
   FROM teams
   ORDER BY .... --this part is fine
   limit 1
 ) AS latest_teams
 ON players.id = teams.player_id
如何讓內部查詢的範圍成為玩家 ID 的範圍？

首先，如果您SELECT只來自teams表並且有一個FOREIGN KEY約束REFERENCES players，那麼您根本不需要加入players。
現在，有多種方法可以編寫這種類型的查詢，甚至在 SO 和 DBA.SE 中都有一個標籤，great -n-per-group。在這種情況下，我們需要最大的 1。最簡單的程式碼是使用DISTINCT ON構造（它是 Postgres，對 SQL 的非標準補充）。
如果您需要來自的列players，您可以簡單地加入並添加選擇列表中的列。：
SELECT DISTINCT ON (t.player_id)
 t.player_id,
 t.team_name,
 t.start_date,
 t.end_date
FROM teams AS t
 -- JOIN players AS p            
 --   ON p.id = t.player_id
ORDER BY 
   t.player_id,  -- this needs to match the DISTINCT ON ()
   ....      -- pick what order you want, so the 1st is chosen for each player
;            -- no LIMIT
在大多數其他 DBMS 中起作用的另一種方法是使用視窗函式。選擇哪一行的標准在OVER子句中：
SELECT 
   g.player_id,
   g.team_name,
   g.start_date,
   g.end_date
FROM
 ( SELECT 
     t.player_id,
     t.team_name,
     t.start_date,
     t.end_date,
     ROW_NUMBER() OVER (PARTITION BY t.player_id 
                        ORDER BY ...) AS rn
   FROM teams AS t
     -- JOIN players AS p            
     --   ON p.id = t.player_id
 ) AS g
WHERE
   g.rn = 1 ;
如果您不在某些舊版本的 Postgres 中，還有LATERAL語法，這通常是最有效的。尤其是當有一個具有不同值的“驅動”表時，我們想要作為分組的基礎（players這裡），並且如果這些值的數量相對較少並且有很多可能的選項（即在這種情況下，最大的teams表是，在與相比players，此查詢更好）。
還要注意這個查詢與您最初的想法非常相似。從字面上看，它確實是您想要的：允許在子查詢players中引用列lt：
SELECT
   lt.player_id,
   lt.team_name,
   lt.start_date,
   lt.end_date
FROM players AS p
 JOIN LATERAL
   ( SELECT t.*
     FROM teams AS t 
     WHERE p.id = t.player_id
     ORDER BY ....     -- pick the order
     LIMIT 1 
  ) AS lt ON TRUE ;