子查询返回每个父ID的最新条目

本文介绍了子查询返回每个父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.