检索每个类别的2个最新帖子 [英] Retrieve 2 last posts for each category

问题描述

让我们说我有2个表:blog_posts和类别.每个博客帖子仅属于一个类别，因此这里的两个表之间基本上有一个外键.

Lets say I have 2 tables: blog_posts and categories. Each blog post belongs to only ONE category, so there is basically a foreign key between the 2 tables here.

我想从每个类别中检索2个最新的帖子，是否可以在单个请求中实现? GROUP BY会将所有内容分组，而在每个类别中只剩下一行.但是我要两个.

I would like to retrieve the 2 lasts posts from each category, is it possible to achieve this in a single request? GROUP BY would group everything and leave me with only one row in each category. But I want 2 of them.

执行1 + N查询(N =类别数)将很容易.首先检索类别.然后从每个类别中检索2个帖子.

It would be easy to perform 1 + N query (N = number of category). First retrieve the categories. And then retrieve 2 posts from each category.

我认为执行M个查询(M =我希望从每个类别获得的帖子数)也非常容易.第一个查询为每个类别(分组依据)选择第一篇文章.第二个查询检索每个类别的第二个帖子.等

I believe it would also be quite easy to perform M queries (M = number of posts I want from each category). First query selects the first post for each category (with a group by). Second query retrieves the second post for each category. etc.

我只是想知道是否有人对此有更好的解决方案.我真的不介意为此进行1 + N查询，但是出于好奇和一般的SQL知识，将不胜感激！

I'm just wondering if someone has a better solution for this. I don't really mind doing 1+N queries for that, but for curiosity and general SQL knowledge, it would be appreciated!

在此先感谢谁能帮助我.

Thanks in advance to whom can help me with this.

推荐答案

查看此 MySQL文章 ，介绍如何处理任意复杂分组中的前N个内容；这是好东西.您可以尝试以下方法:

Check out this MySQL article on how to work with the top N things in arbitrarily complex groupings; it's good stuff. You can try this:

SET @counter = 0;
SET @category = '';

SELECT
  *
FROM
(
  SELECT
    @counter := IF(posts.category = @category, @counter + 1, 0) AS counter,
    @category := posts.category,
    posts.*
    FROM
      (
      SELECT
        *
        FROM test
        ORDER BY category, date DESC
      ) posts
) posts
HAVING counter < 2

这篇关于检索每个类别的2个最新帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！