为每个类别选择N条记录，并按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.

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

所以我的posts表看起来是这样的:id | title | description | cat |文件名|日期

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.

如何创建这样的查询?我曾想过使用group-by或subselect，但我不确定这是否对性能有好处。该表有大量的记录。

2 个解决方案

#1

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

MySQL不支持解析函数(ROW_NUMBER、RANK、DENSE_RANK、NTILE…)，但是可以使用变量来模拟这个功能。

If you want the N most recent blog posts:

如果你想要最近的N篇博文:

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

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

如果你想让排名1成为最早的博客，可以将排名改为:

ORDER BY bp.cat, bp.date

#2

With more modern SQL, using CTE and Windows Functions (tested in PostgreSQL 9.3, but I suspect it'll work on a recent version of MySQL too), to show say 2 title per category:

使用更现代的SQL，使用CTE和Windows函数(在PostgreSQL 9.3中测试过，但我怀疑它也可以在最近的MySQL版本中使用)，显示每个类别2个标题:

WITH b AS
  (SELECT title, cat, row_number() OVER (PARTITION BY cat) as rn FROM BLOGS)
SELECT title, cat FROM b WHERE rn <= 2;

#1