mysql - 仅显示一对多选择中的最佳结果

Question

我无法理解如何处理我在 SQL 中想到的问题。我有书签和用户发布的关于它们的评论。我正在为所有评论使用一个表。所以我们在书签和评论之间有一个一对多的关系。

还有另一个表，充当中间人，将每个书签链接到它的所有评论。

有两种类型的评论。书签的建议标题和一般评论。建议的标题既有标题又有描述，而一般评论只有描述。还有一个推荐标题的评级系统，因此主页可以为每个书签选择评级最高的标题来显示。

所以，主要的事情要弄清楚。有包含 BID 和 URL 的 Bookmarks 表，还有包含 CID、标题、评论和评级的 Comments 表。 BooksNComms 是它们之间的连接表。

    SELECT comments.title, comments.comment
      FROM comments
INNER JOIN booksncomms ON comments.cid=booksncomms.cid
     WHERE booksncomms.bid=1
       AND comments.title is not null
  ORDER BY comments.rating
     LIMIT 0, 1;

以上方法可以为某个 BID 获取最佳标题和描述(评论)。我想要做的是使上述工作适用于 10 个最新的书签。

    SELECT bookmarks.url, comments.title, comments.`comment`, comments.rating
      FROM bookmarks
INNER JOIN booksncomms 
        ON bookmarks.bid=booksncomms.bid
INNER JOIN comments 
        ON comments.cid=booksncomms.cid
      JOIN (
            SELECT bookmarks.bid 
              FROM bookmarks 
          ORDER BY bookmarks.datecreated DESC 
             LIMIT 1
        )
        AS a 
        ON a.bid=bookmarks.bid
     WHERE comments.title IS NOT NULL
  ORDER BY bookmarks.url;

上面给出了 10 个最新书签的所有标题。

有没有一种方法可以只为 10 个最新书签中的每一个选择评分最高的标题？

Answer 1

(OP自己的解决方案，从问题中分离出来)

@LefterisAslanoglou 说:

I realized I knew the answer just a few minutes after I posted the question here. It's been bugging me for hours, but it was a simple matter of getting a table that has the best rated title for each bookmark and then joining that to the one with all the titles for the latest bookmarks.

    SELECT bookmarks.url, comments.title, comments.comment, comments.rating
      FROM bookmarks
INNER JOIN booksncomms ON bookmarks.bid=booksncomms.bid
INNER JOIN comments ON comments.cid=booksncomms.cid
      JOIN (
           SELECT bookmarks.bid 
             FROM bookmarks 
         ORDER BY bookmarks.datecreated DESC 
            LIMIT 10
      ) AS a ON a.bid=bookmarks.bid
      JOIN (
          SELECT comments.cid, MAX(comments.rating) 
              AS maxrating
            FROM comments
      INNER JOIN booksncomms ON comments.cid=booksncomms.cid
        GROUP BY booksncomms.bid
      ) AS b ON b.cid=comments.cid
     WHERE comments.title IS NOT NULL
  ORDER BY bookmarks.datecreated DESC;