mysql - 每个组子集的最大行数

Question

我有一个这样的查询:

SELECT * FROM user AS u 
JOIN article AS a
ON u.id = a.userid
GROUP BY u.id

如何为每个特定用户提取最多 10 篇文章？

Answer 1

Mysql 没有针对此类结果的窗口函数，另一种解决方法是使用用户定义的变量来获取每组的 n 个结果

SELECT * FROM (
SELECT a.*,
@r:= CASE WHEN @g = userid THEN @r + 1 ELSE 1 END row_num,
@g:= userid
FROM (SELECT * 
FROM `user` AS u 
JOIN article AS a
ON u.id = a.userid
ORDER BY u.id,a.id DESC
) a
CROSS JOIN (SELECT @g:=NULL,@r:0) b
) t
WHERE row_num <=10