仅显示已加入的MySQL表中的最新日期 [英] Show only most recent date from joined MySQL table

问题描述

我有2个表，一个文档"表和一个内容"表.它们看起来像这样(简化):

I have 2 tables, a "document" table and a "content" table. They look like this (simplified):

document table:
docID
docTitle

content table:
contentID
docID
dateAdded
content

对于每个内容更改，都会在内容"表中插入一条新记录.这样，所有更改都有完整的历史记录.我想获取所有文档的列表，并加入最新的内容.它应返回docID，docTitle和具有最新"dateAdded"值的关联内容记录.我的大脑现在让我很沮丧，我将如何创建这种联接?

For every content change, a new record is inserted into the "content" table. This way there is a complete history of all changes. I would like to get a list of all the documents, with the latest content joined. It should return the docID, docTitle, and the associated content record with the newest "dateAdded" value. My brain is failing me right now, how would I create this join?

推荐答案

这可以通过子查询完成:

This can be done with a subquery:

SELECT d.docID, docTitle, c.dateAdded, c.content
FROM document d LEFT JOIN content c ON c.docID = d.docID
WHERE dateAdded IS NULL
    OR dateAdded = (
        SELECT MAX(dateAdded)
        FROM content c2
        WHERE c2.docID = d.docID
    )

这被称为分组最大值" 查询

使查询返回所有文档行，如果没有相关内容，则返回NULL.

Made the query return all document rows, with NULLs if there is no related content.

这篇关于仅显示已加入的MySQL表中的最新日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！