子查询返回每个父ID的最新条目 [英] Subquery to return the latest entry for each parent ID

问题描述

我有一个带有文档条目的父表，我有一个历史记录表，每次用户访问其中一个文档时都会记录审核条目。

I have a parent table with entries for documents and I have a history table which logs an audit entry every time a user accesses one of the documents.

m编写搜索查询以返回具有最新用户标识的文档列表（通过各种标准过滤）以访问结果集中返回的每个文档。

I'm writing a search query to return a list of documents (filtered by various criteria) with the latest user id to access each document returned in the result set.

因此， / p>

Thus for

    DOCUMENTS
    ID | NAME
    1  | Document 1
    2  | Document 2
    3  | Document 3
    4  | Document 4
    5  | Document 5

    HISTORY
    DOC_ID | USER_ID | TIMESTAMP
    1      | 12345   | TODAY
    1      | 11111   | IN THE PAST
    1      | 11111   | IN THE PAST
    1      | 12345   | IN THE PAST
    2      | 11111   | TODAY
    2      | 12345   | IN THE PAST
    3      | 12345   | IN THE PAST

我希望从我的搜索中获得回报，如

I'd be looking to get a return from my search like

    ID | NAME       | LAST_USER_ID
    1  | Document 1 | 12345
    2  | Document 2 | 11111
    3  | Document 3 | 12345
    4  | Document 4 | 
    5  | Document 5 | 

我可以轻松地使用一个SQL查询和两个表之间的连接吗？

Can I easily do this with one SQL query and a join between the two tables?

推荐答案

修改Andy White生成的代码，并用DB2（和ISO标准SQL）分隔标识符替换方括号（MS SQL Server表示法）：

Revising what Andy White produced, and replacing square brackets (MS SQL Server notation) with DB2 (and ISO standard SQL) "delimited identifiers":

SELECT d.id, d.name, h.last_user_id
    FROM Documents d LEFT JOIN
         (SELECT r.doc_id AS id, user_id AS last_user_id
              FROM History r JOIN
                   (SELECT doc_id, MAX("timestamp") AS "timestamp"
                        FROM History
                        GROUP BY doc_id
                   ) AS l
                   ON  r."timestamp" = l."timestamp"
                   AND r.doc_id      = l.doc_id
         ) AS h
         ON d.id = h.id

我不是绝对确定时间戳或TIMESTAMP是否正确 - 可能后者。

I'm not absolutely sure whether "timestamp" or "TIMESTAMP" is correct - probably the latter.

这样做的优点在于它用一个更简单的非相关子查询来替代Andy版本中的内部相关子查询，这有可能（从根本上讲）更有效率。

The advantage of this is that it replaces the inner correlated sub-query in Andy's version with a simpler non-correlated sub-query, which has the potential to be (radically?) more efficient.

这篇关于子查询返回每个父ID的最新条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！