mysql - 编辑的 JOIN 查询，以便具有唯一值的字段

Question

我有一个 JOIN 查询，它提取最后 6 个“主题”记录(topic_id 最高的记录的 topic_status 为 0，即它不是垃圾邮件):

SELECT topic_title, topic_slug, meta_value, topic_poster
FROM `folio_topics` as Topics
INNER JOIN `folio_topicmeta` as Topicmeta
ON Topics.topic_id = Topicmeta.topic_id
WHERE `topic_status` = 0
AND Topicmeta.meta_key = 'bb_attach_thumb'
ORDER BY Topics.`topic_id` DESC
LIMIT 6

我想编辑此查询，以便 folio_topics.topic_poster 字段是唯一的，即我不会得到 6 条具有相同 topic_poster 的记录，而是 6 条记录，所有这些记录都具有唯一的 topic_poster 值。

任何帮助将不胜感激! :-)

---

根据 Vincent 的回答，我提出了一个查询，可以为每个 topic_poster 正确提取最高 topic_id，但它需要 8.7 秒，与大多数查询相比非常慢(花费更少小于 0.1 秒)。有没有办法优化这个查询？

SELECT topic_title, topic_slug, meta_value, topic_poster
FROM `folio_topics` AS Topics
INNER JOIN `folio_topicmeta` AS Topicmeta
ON Topics.topic_id = Topicmeta.topic_id
WHERE Topics.topic_id IN (
    SELECT MAX(`topic_id`)
    FROM `folio_topics`
    WHERE `topic_status` = 0
    GROUP BY `topic_poster`)
AND Topicmeta.meta_key = 'bb_attach_thumb'
ORDER BY Topics.`topic_id` DESC
LIMIT 6

Answer 1

根据 this article 你可以使用 Group By 来完成你正在尝试的事情。试试这个

SELECT topic_title, topic_slug, meta_value, topic_poster
FROM `folio_topics` as Topics
INNER JOIN `folio_topicmeta` as Topicmeta
ON Topics.topic_id = Topicmeta.topic_id
WHERE `topic_status` = 0
AND Topicmeta.meta_key = 'bb_attach_thumb'
Group By topic_poster
ORDER BY Topics.`topic_id` DESC
LIMIT 6