如何在 SQL 連線中僅選擇最新的位置資料？

在連接操作中選擇最大日期

要擷取特定記錄的歷史位置，請考慮以下查詢：

SELECT l.location, t.transaction_id, t.date_modified
FROM transactions AS t
INNER JOIN (
  SELECT
    received_id,
    MAX(date_modified) AS maxmodify
  FROM transactions
  GROUP BY
    received_id
) AS max_record
  ON (
    t1.received_id = max_record.received_id
  )
INNER JOIN locations AS l
  ON (
    l.location_id = t.location_id
  )
INNER JOIN received AS r
  ON (
    r.received_id = t.received_id
  )
WHERE
  t.received_id = '1782'
ORDER BY
  t.date_modified DESC;

此查詢傳回給定記錄的位置列表，但它包含同一位置和日期的多個條目。若要確保僅顯示每個位置的最後修改日期，請使用下列查詢：

SELECT t1.received_id, t1.transaction_id, t1.date_modified, l.location
FROM transactions t1
JOIN (
  SELECT
    received_id,
    MAX(date_modified) AS maxmodify
  FROM transactions
  GROUP BY
    received_id
) AS max_record
  ON (
    max_record.received_id = t1.received_id AND max_record.maxmodify = t1.date_modified
  )
JOIN locations AS l
  ON (
    l.location_id = t1.location_id
  )
JOIN received AS r
  ON (
    r.received_id = t1.received_id
  )
WHERE
  t1.received_id = '1782'
ORDER BY
  t1.date_modified DESC;

關鍵修改是新增連線條件，以確保所選的 date_modified 值是每筆記錄的最大值。這可確保僅顯示最近更新的位置資訊。

以上是如何在 SQL 連線中僅選擇最新的位置資料？的詳細內容。更多資訊請關注PHP中文網其他相關文章！