仅显示连接的 MySQL 表中的最近日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1368331/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Show only most recent date from joined MySQL table
提问by Jon Tackabury
I have 2 tables, a "document" table and a "content" table. They look like this (simplified):
我有 2 个表，一个“文档”表和一个“内容”表。它们看起来像这样（简化）：
document table:
docID
docTitle
content table:
contentID
docID
dateAdded
content
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?
对于每次内容更改，都会在“内容”表中插入一条新记录。这样就有了所有更改的完整历史记录。我想获取所有文档的列表，并加入最新的内容。它应该返回 docID、docTitle 和具有最新“dateAdded”值的相关内容记录。我的大脑现在让我失望了，我将如何创建这个连接？
回答by Christian Oudard
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
)
This is known as a "groupwise maximum"query
这称为“分组最大值”查询
Edit: Made the query return all document rows, with NULLs if there is no related content.
编辑：使查询返回所有文档行，如果没有相关内容，则为 NULL。
回答by OMG Ponies
Use:
用：
SELECT t.docid,
t.docTitle,
mc.dateAdded,
mc.content
FROM DOCUMENT t
JOIN (SELECT c.docid,
c.content,
MAX(c.dateAdded)
FROM CONTENT c
GROUP BY c.docid, c.content) mc ON mc.docid = t.docid
AND mc.dateadded = t.dateadded
This should be faster than a correlated subquery.
这应该比相关子查询更快。
Alternative for when there are no content records for a document:
当文档没有内容记录时的替代方法：
SELECT t.docid,
t.docTitle,
mc.dateAdded,
mc.content
FROM DOCUMENT t
LEFT JOIN (SELECT c.docid,
c.content,
MAX(c.dateAdded)
FROM CONTENT c
GROUP BY c.docid, c.content) mc ON mc.docid = t.docid
AND mc.dateadded = t.dateadded
回答by Zoidberg
Could you not just do a simple join, and order by date added, and grab only the first record?
你能不能做一个简单的连接，按添加的日期排序，然后只抓取第一条记录？
SELECT docTable.docId, docTable.docTitle from docTable
INNER JOIN content ON content.docID = docTable.contentID
WHERE docTable.docId = <some id>
ORDER BY content.dateAdded DESC
回答by PowerUser
This is a 2 query solution:
这是一个 2 查询解决方案：
First query:
第一个查询：
select docID, max(dateadded) from [TblContent] group by docID
Second query:
第二个查询：
select [TblDocument].* from [TblDocument]
inner join [TblDocument] on [TblDocument].[Docid]=[FirstQuery].[DocID]
回答by ez.
try this:
尝试这个：
select d.id, d.docTitle, MAX(c.dateAdd)
from document_table d
left join content_table c
on d.id = c.docId
group by d.id
Here is the thinking behind it: suppose document table has record A related to content(1, 2, 3, 4) and B related to (5, 6, 7, 8)
这是它背后的想法：假设文档表有与内容（1、2、3、4）相关的记录 A 和与（5、6、7、8）相关的记录
document content
A 1
2
3
4
B 5
6
7
8
a inner join with max(dateadded) will give you
与 max(dateAdded) 的内部连接会给你
document content max(dateadded)
A 1 1-1-2009...
A 2 1-1-2009...
A 3 1-1-2009...
A 4 1-1-2009...
B 5 2-1-2009...
B 6 2-1-2009...
B 7 2-1-2009...
B 8 2-1-2009...
after group by document id you will get
按文档 ID 分组后，您将获得
document content max(dateadded)
A 1 1-1-2009...
B 5 2-1-2009...
note: content id does not necessary match the id of the max dateadded
注意：内容 id 不必与添加的最大日期的 id 匹配
