连接表上的最大日期问题

Question

例如，使用日期：03-16-2011 作为今天的日期

表记录：record_id、record_status、record_subscribed_date

表 record_change_log：change_log_id、record_id、new_record_status、record_change_date、old_record_status

（将只有一个 record_id） 表记录数据：（

1, key, 2011-03-14 08:24:12.04051-04
2, manual, 2011-03-15 09:56:32.04051-04
3, entered, 2011-03-16 07:44:01.04051-04

将有多个record_id和record_change_date） 表 record_change_log 数据：

100, 3, entered, 2011-03-16 09:54:01.04051-04, ''
101, 3, updated, 2011-03-16 09:55:01.04051-04, entered
102, 2, manual, 2011-03-15 09:56:32.04051-04, ''
103, 3, valid, 2011-03-16 10:00:01.04051-04, updated
104, 2, updated, 2011-03-15 10:01:51.04051-04, manual
105, 3, returned, 2011-03-16 10:11:22.04051-04, valid
106, 1, updated, 2011-03-16 11:11:11.04051-04, key
107, 23, manual, ...
108, 100, test, ...

我需要的是每天返回所有 record_id 以及最后输入的更改日志。 因此，record_id 1 将返回：

106, 1, updated, 2011-03-16 11:11:11.04051-04, key

并且 record_id 2 不会返回，因为日期不是今天：

104, 2, updated, 2011-03-15 10:01:51.04051-04, manual

并且 record_id 3 将返回：

105, 3, returned, 2011-03-16 10:11:22.04051-04, valid

但在查询中，我只需要返回的 record_id 和 new_record_status 已随今天的日期更改，因此 record_id 2 将不会在结果中返回，但 1 和 3 会返回。

现在查询的另一部分是我只需要记录表中的 record_id，因此更改日志表中的其他记录也不会返回：

107, 23, manual, ...
108, 100, test, ...

因此，所需的结果是这些是更改日志中的最后一个条目表以及它们是记录表中的记录：

105, 3, returned, 2011-03-16 10:11:22.04051-04, valid
106, 1, updated, 2011-03-16 11:11:11.04051-04, key

我尝试连接表，但我似乎无法限制更改日志表中的结果。我可以限制更改日志表中的结果，但无法选择我需要的所有记录，这会导致 GROUP BY 抱怨某些事情

Answer 1

您可以加入相关的子查询，例如...

SELECT
  *
FROM
  record
INNER JOIN
  record_change_log
    ON record_change_log.change_log_id = (
      SELECT
        change_log_id
      FROM
        record_change_log
      WHERE
        record_id = record.record_id
      ORDER BY
        record_change_date DESC
      LIMIT
        1
    )

（我将“仅限今天”部分留给您，因为我认为“更改日志中只有一条记录”是您需要帮助的部分。 ）

Answer 2

--
-- Pull today's most recent change log entries for every known "record_id",
-- if any.
--
SELECT *
  FROM record_change_log
 INNER JOIN (
             -- Find the timestamp of the most recent change log entry
             -- for each known "record_id" (in the "record" table),
             -- which change occurred today.
             SELECT record_id,
                    max(record_change_date) as changed
               FROM record_change_log
              INNER JOIN
                    record USING (record_id)
              WHERE record_change_date::DATE = CURRENT_DATE
              GROUP BY 1
            ) most_recent ON most_recent.record_id = record_change_log.record_id
                             AND
                             record_change_date = most_recent.changed;