列出所有'feeds'并且只有2个有关系的用户

Question

我需要列出表feed中的所有项目，并且只显示订阅内容的前2个用户，但是我不能将查询放在一起列表中只有2个用户limit 2。

我试过N querys和子查询，但无法得到预期的结果。最近的是使用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:[email protected]+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 

您可以生成每组行号，然后选择每饲料ID第2行。

Answer 2

您可以使用变量来模拟row_number()给每个进每个用户一个“等级”，只有为了做分组之前选择与数< = 2行只能获得每组2个用户：

select id, type, group_concat(name) from (
    select * from (
     select *, @rn := if(@prevFeedId = id, @rn+1, 1) as rn, 
     @prevFeedId := id 
     from (
      select 
      feed.id 
      , feed.type 
      , user.name 
      from feed 
      inner join user on user.id = feed.user 
     ) t1 order by id 
    ) t1 where rn <= 2 
) t1 group by id, type 

Answer 3

我不知道你的表的完全模式，但也尝试你已经尝试过与GROUP_CONCAT相同的方法，但加入像一个子查询：

... 
inner join 
(
    select user.id, user.name from user limit 2 
) as x on x.id = feed.user 
...