SQL 在某个日期后获取每个 ID 的最后一条记录

Question

我需要帮助创建一个 SQL 语句来检索 USER_LOG 表中每个 USERID 某个日期后的最后一条记录。

我忘了写这个表中还有一些其他列。

TABLE: USER_LOG

ID  NAME    MODIFY_DATE            MODIFY_TYPE 55  userA   2013-05-07 15:47:53.0  188  userB   2013-05-07 16:00:57.0  155  userA   2013-05-08 11:44:10.0  288  userB   2013-05-08 15:47:09.0  2 88  userB   2013-05-08 16:01:41.0  1    55  userA   2013-05-09 15:11:53.0  055  userA   2013-05-09 16:00:57.0  055  userA   2013-05-10 09:14:10.0  188  userB   2013-05-10 16:01:41.0  255  userA   2013-05-10 18:23:03.0  255  userA   2013-05-11 09:14:10.0  288  userB   2013-05-11 16:01:41.0  155  userA   2013-05-13 11:34:07.0  155  userA   2013-05-13 15:53:04.0  255  userA   2013-05-13 16:13:04.0  1

Example 1: Get All users they have bean changed after '2013-05-08 00:00:00.0'.
Must return:

ID  NAME    MODIFY_DATE            MODIFY_TYPE 55  userA   2013-05-07 15:47:53.0  188  userB   2013-05-07 16:00:57.0  155  userA   2013-05-08 11:44:10.0  288  userB   2013-05-08 15:47:09.0  2 88  userB   2013-05-08 16:01:41.0  1    55  userA   2013-05-09 15:11:53.0  055  userA   2013-05-09 16:00:57.0  055  userA   2013-05-10 09:14:10.0  188  userB   2013-05-10 16:01:41.0  255  userA   2013-05-10 18:23:03.0  255  userA   2013-05-11 09:14:10.0  2**88    userB   2013-05-11 16:01:41.0  1**  RETURN THIS55  userA   2013-05-13 11:34:07.0  155  userA   2013-05-13 15:53:04.0  2**55    userA   2013-05-13 16:13:04.0  1**  RETURN THIS

Example 2: Get All users they have bean changed after '2013-05-12 00:00:00.0'.
Must return:

ID  NAME    MODIFY_DATE            MODIFY_TYPE 55  userA   2013-05-07 15:47:53.0  188  userB   2013-05-07 16:00:57.0  155  userA   2013-05-08 11:44:10.0  288  userB   2013-05-08 15:47:09.0  2 88  userB   2013-05-08 16:01:41.0  1    55  userA   2013-05-09 15:11:53.0  055  userA   2013-05-09 16:00:57.0  055  userA   2013-05-10 09:14:10.0  188  userB   2013-05-10 16:01:41.0  255  userA   2013-05-10 18:23:03.0  255  userA   2013-05-11 09:14:10.0  288  userB   2013-05-11 16:01:41.0  1  55  userA   2013-05-13 11:34:07.0  155  userA   2013-05-13 15:53:04.0  2**55    userA   2013-05-13 16:13:04.0  1**  RETURN THIS

我找到了一些东西，但我不知道我们把带有日期的条件放在 X 之后的什么地方:


SELECT u1.*
FROM user_log u1 LEFT JOIN user_log u2
ON (u1.id = u2.id AND u1.modify_date < u2.modify_date )
WHERE u2.modify_date IS NULL;


有人可以帮我解决这个问题吗？

Answer 1

嗯，为了这些目的本身analytical functions被发明了。

给你:

SELECT DISTINCT
  ID,
  NAME, 
  LAST_VALUE(MODIFY_DATE) OVER (PARTITION BY ID ORDER BY MODIFY_DATE ROWS BETWEEN unbounded preceding and unbounded following) MODIFY_DATE
FROM
  USER_LOG
WHERE
  MODIFY_DATE > :date