mysql - 如何在此查询中获得 DISTINCT

Question

这是我的查询:

SELECT phpbb_attachments.attach_id, phpbb_attachments.post_msg_id
FROM phpbb_attachments
INNER JOIN phpbb_topics 
    ON phpbb_attachments.topic_id = phpbb_topics.topic_id
INNER JOIN phpbb_forums ON phpbb_topics.forum_id = phpbb_forums.forum_id
WHERE phpbb_attachments.is_orphan = 0
AND phpbb_attachments.thumbnail = 1
AND (phpbb_attachments.extension = 'jpg'
OR phpbb_attachments.extension = 'png'
OR phpbb_attachments.extension = 'gif')
AND phpbb_forums.forum_id BETWEEN 668 AND 679
ORDER BY phpbb_attachments.attach_id DESC
LIMIT 50

我只想获取每个主题最新的 1 个附件。有点像 phpbb_attachments.topic_id 上的不同

Answer 1

此查询给出了每个主题的“最佳”附件:

SELECT MAX(a.attach_id), t.topic_id
  FROM phpbb_attachments a
    INNER JOIN phpbb_topics t ON a.topic_id = t.topic_id
    INNER JOIN phpbb_forums f ON t.forum_id = f.forum_id
 WHERE a.is_orphan = 0
   AND a.thumbnail = 1
   AND (a.extension = 'jpg' OR a.extension = 'png' OR a.extension = 'gif')
   AND f.forum_id BETWEEN 668 AND 679
 GROUP BY t.topic_id

然后从该列表中选择附件:

SELECT x.attach_id, x.post_msg_id
FROM phpbb_attachments x INNER JOIN (SELECT MAX(a.attach_id) AS attach_id, t.topic_id
  FROM phpbb_attachments a
    INNER JOIN phpbb_topics t ON a.topic_id = t.topic_id
    INNER JOIN phpbb_forums f ON t.forum_id = f.forum_id
 WHERE a.is_orphan = 0
   AND a.thumbnail = 1
   AND (a.extension = 'jpg' OR a.extension = 'png' OR a.extension = 'gif')
   AND f.forum_id BETWEEN 668 AND 679
 GROUP BY t.topic_id) i ON x.attach_id = i.attach_id
 ORDER BY x.attach_id DESC
 LIMIT 50