使用引用主表的子查詢進行更新

我試圖了解如何在主查詢的上下文中過濾我的子查詢。最終，我試圖在目前記錄日期之前從最新記錄中獲取 MAX 值。這就是我所在的地方。

UPDATE Opphistory t
SET    MaxStageSortOrder = sub.max_snapshotdate
FROM  (
  SELECT opportunityid, max(snapshotdate) AS max_snapshotdate
  FROM   Opphistory
  WHERE forecastcategory <> 'Omitted' and snapshotdate <= t.snapshotdate
  GROUP  BY 1
  ) sub
WHERE t.opportunityid = sub.opportunityid

這snapshotdate <= t.snapshotdate似乎是失敗的。

似乎您的目標是相關子查詢：

UPDATE opphistory t
SET    MaxStageSortOrder = (
  SELECT max(snapshotdate)
  FROM   Opphistory t1
  WHERE  t1.opportunityid = t.opportunityid
  AND    t1.snapshotdate < t.snapshotdate  -- "before", so not <=
  AND    t1.forecastcategory <> 'Omitted'
  );

FROMan 子句中的派生表UPDATE不能引用主表的列。這在相關子查詢或LATERAL子查詢中是可能的。但是，不幸的是FROM，an 子句中的表表達式UPDATE（至少到第 14 頁）總是與 a CROSS JOIN（有效地）連接到主表。FROM您可以通過在子句中重複主表，將其一對一綁定到主表，然後使用任何連接類型連接到“代理”來解決此限制。在手頭的情況下，我們甚至不需要：

UPDATE Opphistory t
SET    MaxStageSortOrder = sub.max_snapshotdate
FROM  (
  SELECT opphistory_id
       , max(snapshotdate) OVER (PARTITION BY opportunityid ORDER BY snapshotdate 
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS max_snapshotdate
  FROM   Opphistory
  WHERE  forecastcategory <> 'Omitted'
  ) sub
WHERE  t.opphistory_id = sub.opphistory_id
AND    t.MaxStageSortOrder IS DISTINCT FROM sub.max_snapshotdate;

opphistory_id應該是PRIMARY KEY表的。

我希望第二個查詢對於大表來說要快得多，因為它（可能）只需要一個順序掃描和一個排序來計算所有行的執行最大值。為每一行執行一個相關的子查詢（就像在第一個查詢中一樣）有它的代價。

關於ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING：

• 將結果減少到累積的組中

注意細微差別：

• 無論如何，第一個查詢都會更新每一行。

• 第二個查詢省略…

  • … 行forecastcategory = 'Omitted'和行forecastcategory IS NULL

  （第一個查詢僅從最大計算中排除那些。）

  • …行opportunityid IS NULL
  • …不會改變的行 - 由於添加了最後一行。

如果(opportunityid, snapshotdate)未定義UNIQUE NOT NULL，我們可能需要做更多的事情，從定義如何處理重複值和 NULL 值開始。

從您的問題中不清楚您到底想要什麼。

如果您真的只想要snapshotdate“上一個”行中的，請考慮更簡單的視窗函式**lag()**：

UPDATE Opphistory t
SET    MaxStageSortOrder = sub.max_snapshotdate
FROM  (
  SELECT opphistory_id
       , lag(snapshotdate) OVER (PARTITION BY opportunityid ORDER BY snapshotdate) AS max_snapshotdate
  FROM   Opphistory
  WHERE  forecastcategory <> 'Omitted'
  ) sub
WHERE  t.opphistory_id = sub.opphistory_id
AND    t.MaxStageSortOrder IS DISTINCT FROM sub.max_snapshotdate;

子查詢必須是相關的或不相關的，不能介於兩者之間。在這種情況下，它似乎需要關聯。

UPDATE Opphistory t
SET    MaxStageSortOrder = (
  SELECT max(snapshotdate)                    
  FROM   Opphistory sub
  WHERE forecastcategory <> 'Omitted' and 
        snapshotdate <= t.snapshotdate and 
        t.opportunityid = sub.opportunityid
);

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