为每个类别选择N条记录，并按X排序 [英] Select N records for each category and order by X

问题描述

我有一个包含博客文章的数据库表.我想在首页上显示每个类别的一则(或更多)帖子，例如按日期排序.

I have a database table that contains blog posts. I want to show on the homepage one (or more) post for each category, ordering by date, for example.

所以我的帖子表如下:id | title | description | cat | filename | date

So my posts table looks like this: id | title | description | cat | filename | date

我将如何创建这样的查询?我曾经考虑过使用group-by或subselect，但是我不确定这是否对性能有好处……表中有大量记录.

How would I create such a query? I've thought to use group-by or a subselect but I'm not sure if it's a good thing for performance... the table has a large number of records.

推荐答案

MySQL 不不支持分析功能(ROW_NUMBER，RANK，DENSE_RANK，NTILE ...)，但是您可以模拟该功能带有变量.

MySQL doesn't support analytic functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE...), but you can emulate the functionality with variables.

如果您要 N 最近的博客文章:

If you want the N most recent blog posts:

SELECT x.id,
       x.title,
       x.description,
       x.cat,
       x.filename,
       x.date
  FROM (SELECT bp.id,
               bp.title,
               bp.description,
               bp.cat,
               bp.filename,
               bp.date,
               CASE 
                 WHEN bp.cat = @category THEN @rownum := @rownum + 1
                 ELSE @rownum := 1
               END AS rank,
               @category := bp.cat
          FROM BLOG_POSTS bp
          JOIN (SELECT @rownum := 0, @category := NULL) r
      ORDER BY bp.cat, bp.date DESC) x
 WHERE x.rank <= N

如果您希望等级1成为最早的博客文章，请将ORDER BY更改为:

If you want rank of 1 to be the earliest blog post, change the ORDER BY to:

ORDER BY bp.cat, bp.date

这篇关于为每个类别选择N条记录，并按X排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！