mysql - 列出所有 'feeds' 且仅 2 个有关系的用户

Question

我需要列出表 feed 中的所有项目，并仅显示订阅该内容的前 2 个用户，但我无法组合一个仅执行该列表的查询2 位用户限制 2。

我尝试了N个查询和子查询，但无法获得预期结果。最接近的是使用group_concat，但如果它连接所有个用户并且不允许仅限于两个初始，则必须使用substring_index这个目的。

_查询

select
  feed.id
, feed.type
, user.name

 from feed
 inner join user on user.id = feed.user
 group by feed.type

<小时/>

_结果

Array(
    [0] => Array(
            [id] => 1
            [type] => Comedy
            [name] => Mike
        )

    [1] => Array(
            [id] => 3
            [type] => War
            [name] => John
        )

    [2] => Array(
            [id] => 6
            [type] => Terror
            [name] => Sophia
        )
)

<小时/>

_预期

Array(
    [0] => Array(
            [id] => 1
            [type] => Comedy
            [name] => Mike, Papa
        )

    [1] => Array(
            [id] => 3
            [type] => War
            [name] => John, Alex
        )

    [2] => Array(
            [id] => 6
            [type] => Terror
            [name] => Sophia, Jessica
        )
)

Answer 1

set @rn=0;
select id, type, name 
from 
(
select 
@rn:=@rn+1 AS r_n
,feed.id
,feed.type
,user.name
from feed
inner join user on user.id = feed.user
group by feed.id
order by feed.id) t
where t.r_n <= 2

您可以为每个组生成行号，然后选择每个 Feed ID 的前 2 行。