如何在 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中文网其他相关文章！