子查询返回每个父 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.
我正在编写一个搜索查询来返回一个文档列表(按各种标准过滤),其中包含最新的用户 ID,以访问结果集中返回的每个文档.
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.
因此对于
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 生成的内容,并将方括号(MS SQL Server 表示法)替换为 DB2(和 ISO 标准 SQL)分隔标识符":
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"或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屋!