MySQL查询获取每个类别中的最后一条记录

Question

我有两个表:文章和类别(大约20个类别)，我想获取最新的10篇文章，但一个类别中不超过一篇。

表格文章

id | title | text

表格类别

id | name

Table ArticlesCategories(文章和类别之间的关系表)

article | category

我使用下面的查询，但问题是这 10 条结果并不都是最新的文章。

SELECT id, title, categoryId, categoryName
FROM ( 
        SELECT a.id, a.title, ac.category AS categoryId, c.name AS categoryName
        FROM articles AS a 
            LEFT JOIN articles_categories AS ac ON ac.article = a.id 
            LEFT JOIN categories AS c ON c.id = ac.category 
        WHERE ac.priority = 1 
        ORDER BY a.id DESC ) AS tmp_table 
GROUP BY categoryId LIMIT 10

Answer 1

将 ORDER BY id DESC 添加到您的查询

获取最后 10 行的方法是颠倒顺序并选择前 10 行:

SELECT id, title, categoryId, categoryName
FROM ( 
        SELECT a.id, a.title, ac.category AS categoryId, c.name AS categoryName
        FROM articles AS a 
            LEFT JOIN articles_categories AS ac ON ac.article = a.id 
            LEFT JOIN categories AS c ON c.id = ac.category 
        WHERE ac.priority = 1 
        ORDER BY a.id DESC ) AS tmp_table 
GROUP BY categoryId  ORDER BY id DESC LIMIT 10