MySql 查询:从表中为每个类别选择前 3 行

2021-11-20 00:00:00 limit mysql view

我有一个包含记录的表，其中有一行名为 category.我插入了太多文章，我只想从每个类别中选择两篇文章.

I have a table with records and it has a row called category. I have inserted too many articles and I want to select only two articles from each category.

我试图做这样的事情:

我创建了一个视图:

CREATE VIEW limitrows AS 
   SELECT * FROM tbl_artikujt ORDER BY articleid DESC LIMIT 2

然后我创建了这个查询:

Then I created this query:

SELECT * 
FROM tbl_artikujt 
WHERE 
   artikullid IN
   (
      SELECT artikullid
      FROM limitrows
      ORDER BY category DESC
   )
ORDER BY category DESC;

但这不起作用并且只给我两条记录?

But this is not working and is giving me only two records?

推荐答案

LIMIT 只停止语句返回的结果数.您正在寻找的通常称为分析/窗口/排名函数 - MySQL 不支持，但您可以使用变量进行模拟:

LIMIT only stops the number of results the statement returns. What you're looking for is generally called analytic/windowing/ranking functions - which MySQL doesn't support but you can emulate using variables:

SELECT x.*
  FROM (SELECT t.*,
               CASE 
                 WHEN @category != t.category THEN @rownum := 1 
                 ELSE @rownum := @rownum + 1 
               END AS rank,
               @category := t.category AS var_category
          FROM TBL_ARTIKUJT t
          JOIN (SELECT @rownum := NULL, @category := '') r
      ORDER BY t.category) x
 WHERE x.rank <= 3

如果您不更改 SELECT x.*，结果集将包括 rank 和 var_category 值 - 您将拥有如果不是这种情况，请指定您真正想要的列.

If you don't change SELECT x.*, the result set will include the rank and var_category values - you'll have to specify the columns you really want if this isn't the case.

相关文章