mysql - Select 10 rows for each id in

Question

How I can get 10 rows for each id_recipient ?

SELECT id FROM msg WHERE id_recipient IN (1,2,3,4,5)

So request should return 50 rows(10 rows for each id_recipient)

标签： mysql

Answer 1

您可以使用ROW_NUMBER（MySQL 8.0+）：

SELECT sub.id
FROM (SELECT id,
         ROW_NUMBER() OVER(PARTITION BY id_recipient ORDER BY some_col) AS rn
     FROM msg 
     WHERE id_recipient IN (1,2,3,4,5)) AS sub
WHERE rn <= 10;