如何加入表格中的最新记录? [英] How do I join to the latest record in the table?

问题描述

我需要做的很简单...但是凌晨3点和即时消息可能忽略了显而易见的事情.

What I need done is simple... but its 3am and Im probably overlooking the obvious.

我正在编写一个简单的论坛.一个表存储论坛标题，描述等，而另一个表存储帖子.在显示所有论坛列表的论坛列表中，我想获取每个论坛中的最新帖子，并显示帖子主题，海报和帖子ID和日期.很简单.

Im coding a simple forum. One table stores the forum titles, descriptions, etc, while the other stores the posts. In the forum listing, that shows the list of all forums, I want to grab the latest post in each forum, and display the post subject, poster and post ID, and date. Simple.

唯一的问题是，当我加入posts表时，它会联接到表中的第一个记录，而不是最后一个，即表示该论坛中的最后一个帖子.

The only problem is, when I join to the posts table, it joins to the first record in the table, not the last, which would denote the last post in that forum.

这是简化的查询，它获取最新"帖子(现在用作第一篇帖子")的论坛和数据列表.

Here is the simplified query that gets a list of forums + data for the "latest" post (which now functions as "first post").

SELECT forum_title, forum_id, post_subject, post_user, post_id, post_date FROM board_forums 
     LEFT JOIN board_posts 
     ON (forum_id = post_parentforum AND post_parentpost = 0) 
WHERE forum_status = 1
GROUP BY forum_id
ORDER BY forum_position

我该如何解决?

推荐答案

您遇到的问题是经典的 Ambiguous GROUP BY 问题.这是MySQL特有的，因为其他RDBMS(和标准SQL)根本不允许您进行查询.您的查询未通过单值规则"，因为您没有在GROUP BY中列出所有未汇总的列.

The problem you're hitting is the classic Ambiguous GROUP BY issue. This is particular to MySQL, because other RDBMS (and standard SQL) won't allow your query at all. Your query fails the Single-Value Rule, because you haven't listed all non-aggregated columns in the GROUP BY.

这是一种解决方案，展示了我最喜欢的每组获得最大排行的方法:

Here's a solution demonstrating my favorite way of getting greatest row per group:

SELECT f.forum_title, f.forum_id, p1.post_subject, p1.post_user, 
  p1.post_id, p1.post_date 
FROM board_forums f
LEFT JOIN board_posts p1
  ON (f.forum_id = p1.post_parentforum AND p1.post_parentpost = 0)
LEFT JOIN board_posts p2
  ON (f.forum_id = p2.post_parentforum AND p2.post_parentpost = 0 
      AND p1.post_id < p2.post_id)
WHERE p2.post_id IS NULL AND f.forum_status = 1
ORDER BY f.forum_position;

如果为p2.post_id IS NULL，则表示在p2中找不到帖子，该帖子大于在p1中找到的帖子.

If p2.post_id IS NULL, that means no post is found in p2 which is greater than the post found in p1.

Ergo，p1是最新帖子(假设post_id是自动递增).

Ergo, p1 is the latest post (assuming post_id is auto-incrementing).

评论:

与此有关的轻微问题. ID最高的post_id不一定是最新的帖子.

Slight problem with this. post_id with the highest ID is not necessarily the latest post.

没问题.只需使用保证将较早的帖子与较晚的帖子区分开的列即可.您提到了post_date.如果是平局，则必须中断与按时间顺序排列的另一列(或多列)的关系.

No problem. Just use a column that is guaranteed to distinguish an earlier post from a later post. You mention post_date. In the case of ties, you'll have to break ties with another column (or columns) that will be sure to be in chronological order.

LEFT JOIN board_posts p2
  ON (f.forum_id = p2.post_parentforum AND p2.post_parentpost = 0 
    AND (p1.post_date < p2.post_date 
      OR p1.post_date = p2.post_date AND p1.post_millisecond < p2.post_millisecond))

这篇关于如何加入表格中的最新记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！