如何为每个外键选择数量有限的行? [英] How to select a limited amount of rows for each foreign key?

问题描述

我有这张桌子:

id
feed_id
...

比方说，我有500行，我想为每个feed_id选择3个条目吗?还有50个上限.

Let's say that I have 500 rows and I want to select 3 entries for each feed_id? And 50 as total limit.

如何编写此SQL?

推荐答案

使用:

SELECT x.feedid
  FROM (SELECT t.feedid,
               CASE WHEN @feed != t.feedid THEN @rownum := 1 ELSE @rownum := @rownum + 1 END AS rank,
               @feed := t.feedid
          FROM TABLE t
          JOIN (SELECT @rownum := NULL, @feed := 0) r
      ORDER BY t.feedid) x
 WHERE x.rank <= 3
 ORDER BY x.feedid
 LIMIT 50

尚不清楚要返回什么的详细信息-表中的所有行，或仅是feedid.

What's not clear is the details of what you want returned - all the rows in your table, or just the feedid.

这篇关于如何为每个外键选择数量有限的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！