如何解決 PostgreSQL 的「SELECT DISTINCT ON 表達式必須與初始 ORDER BY 表達式相符」錯誤？

PostgreSQL DISTINCT ON 錯誤：不符 ORDER BY 子句

將PostgreSQL 的DISTINCT ON 與包含不同表達式的ORDER BY 子句一起使用可能會導致錯誤：“SELECT DISTINCT ON 表達式必須與初始ORDER BY 表達式匹配。”發生這種情況是因為DISTINCT ON 根據指定的欄位選擇唯一行，而ORDER BY 指示這些唯一行的呈現順序。 因此，DISTINCT ON 中的欄位必須與 ORDER BY 子句中的前導列相符。

例如，如果您需要擷取不同的address_id 值，同時對具有最新purchased_at 時間戳的行進行優先排序，那麼在使用address_id 時，僅按ORDER BY 子句中的DISTINCT ON (address_id) 進行排序是不夠的。 這是因為 DISTINCT ON 會根據 子句為遇到的每個 選擇 address_id第一個 ORDER BY 行。

這裡有兩個有效的解決方法：

方法一：子查詢求最大值purchased_at

此方法使用子查詢來尋找每個 purchased_at 的最大值 address_id，然後將此結果連接回原始表以選擇對應的行：

SELECT t1.*
FROM purchases t1
JOIN (
    SELECT address_id, MAX(purchased_at) AS max_purchased_at
    FROM purchases
    WHERE product_id = 1
    GROUP BY address_id
) t2
ON t1.address_id = t2.address_id AND t1.purchased_at = t2.max_purchased_at
ORDER BY t1.purchased_at DESC;

方法 2：使用 DISTINCT ON 和 ORDER BY

巢狀查詢

這個 PostgreSQL 特定的解決方案使用巢狀查詢。內部查詢使用 DISTINCT ON 選擇所需的唯一行，並進行適當排序，外部查詢重新排序結果：

SELECT *
FROM (
  SELECT DISTINCT ON (address_id) *
  FROM purchases
  WHERE product_id = 1
  ORDER BY address_id, purchased_at DESC
) t
ORDER BY purchased_at DESC;

兩種方法都提供了高效且準確的結果，克服了直接組合 DISTINCT ON 和不匹配的 ORDER BY 子句帶來的限制。 選擇最適合您的編碼風格和資料庫效能要求的方法。

以上是如何解決 PostgreSQL 的「SELECT DISTINCT ON 表達式必須與初始 ORDER BY 表達式相符」錯誤？的詳細內容。更多資訊請關注PHP中文網其他相關文章！