mysql - 为每个帖子选择最后三个评论者(每组中最大的 n 个)

Question

MySQL

SELECT DISTINCT comments.commenter_id FROM comments WHERE ((oid IN (421,425) 
AND otype = 'post') (oid IN (331) AND otype = 'photo')) ORDER BY  
post_id,type,comment_id LIMIT 3

我想要做的是为每个带有各自 ID 的帖子或照片选择最后三个不同的评论者。即每个 o_id 和 o_type 组合最多有 3 个评论者

但是上面的内容并没有给我带来最后三个不同的评论者，而是给我带来了总共三个。我哪里出错了？谁能帮我吗？

如果限制为 2

ID | oid | otype | commenter_id
1    1      post    1
2    1      post    1
3    1      post    2
4    1      post    3
5    2      post    1
6    1      photo   2
7    2      post    3

输出应该是

commenter_id| o_type | o_id
 3            post      1   
 2            post      1
 3            post      2
 1            post      2
 2            photo     1    

Answer 1

已解决 - 每组人数最多

这很容易! :P

This question对我帮助很大。

SELECT DISTINCT t.commenter_id,t.o_id,t.otype
from 
(
SELECT c.*,
    @row_number:=if(@post_id = oid, @row_number + 1, 1) AS row_number,
       @oid:=oid AS varval
FROM comments c
join (select @row_number := 0, @oid:= NULL) as var
    ON 
    ((oid IN (425) AND otype = 'post') OR (oid IN (331) AND otype = 'photo'))

order by comment_id DESC
) t
where t.row_number <=2