mysql : Algorithm to select most recent item per key in a history table

Question

我面临算法问题。我有一张表，这是我没有建立的历史表。该表具有这种结构:

id - 文件 - 评论日期 - 评论 - 作者
1 - 1 - 20180901 - 嗨 - 2
2 - 1 - 20170901 - 你好 - 3
3 - 2 - - -
4 - 2 - 20160504 - 大家好-4

每个文件都可以有多个评论，但我只想为每个文件选择一行并记录最近的评论。

我尝试了一组不同的组合:group by、having、where、max...但无法获得预期的结果。

我想这只是一个知道的技巧，但我不知道......

欢迎任何帮助!

谢谢文森特

Answer 1

您可以通过排除连接来完成此操作:

SELECT c1.id, c1.file, c1.comment_date, c1.comment, c1.author
FROM comments c1
LEFT JOIN comments c2
ON c2.file = c1.file AND c2.id > c1.id 
WHERE c2.id IS NULL

我使用 id 列而不是 comment_date 列来确定每个文件的最新行，因为可能存在重复的 comment_date 行code> 每个文件的值。

如果您必须使用 comment_date，那么您可以使用此查询，如果存在重复的日期，该查询会回退到 id:

SELECT c1.id, c1.file, c1.comment_date, c1.comment, c1.author
FROM comments c1
LEFT JOIN comments c2
ON c2.file = c1.file AND c2.comment_date > c1.comment_date AND c2.id > c1.id 
WHERE c2.id IS NULL