mysql - 按两列为每个用户组选择 3 条记录

Question

我一直陷入复杂的 MySQL 查询中。这是我的表格:

+--------------------------------------+
| id | user_id | category_id | post_id |
+--------------------------------------+
| 1  | 23      | 5           | 213     |
| 2  | 23      | 5           | 214     |
| 3  | 23      | 5           | 215     |
| 4  | 23      | 5           | 216     |
| 5  | 23      | 6           | 217     |
| 6  | 23      | 6           | 218     |
| 7  | 23      | 6           | 219     |
| 8  | 23      | 6           | 220     |
| 9  | 55      | 13          | 221     |
| 10 | 55      | 13          | 222     |
| 11 | 55      | 16          | 223     |
| 12 | 55      | 16          | 234     |
| 13 | 55      | 22          | 235     |
| 14 | 55      | 22          | 256     |
| 15 | 55      | 22          | 261     |
| 16 | 62      | 13          | 272     |
| 17 | 62      | 13          | 273     |
| 18 | 62      | 24          | 277     |
| 19 | 62      | 24          | 278     |
| 20 | 62      | 24          | 288     |
| 21 | 62      | 31          | 289     |
| 22 | 62      | 31          | 290     |
+--------------------------------------+

现在我希望对于每个 user_id 我想要 2 行数据，但每行应该有不同的 category_id，如下面的结果集:

+--------------------------------------+
| id | user_id | category_id | post_id |
+--------------------------------------+
| 1  | 23      | 5           | 213     |
| 5  | 23      | 6           | 217     |
| 9  | 55      | 13          | 221     |
| 11 | 55      | 16          | 223     |
| 16 | 62      | 13          | 272     |
| 18 | 62      | 24          | 277     |
+--------------------------------------+

到目前为止，我使用 GROUP BY 子句使用的查询只能为每个组返回一行，但我想要 2 或可能 3。这是我的查询:

SELECT * FROM (
    SELECT id, user_id, category_id, post_id 
    FROM my_table
    GROUP BY user_id, category_id) 
AS sub GROUP BY sub.user_id;

请建议如何从这里出发...

Answer 1

您可以使用用户定义的变量来为同一用户组提供排名，并且在外部查询中，您可以根据需要简单地使用条件来显示每个用户 2 个类别或 3 个或更多类别

SELECT id,
  user_id,
  category_id,
  post_id,
  rank FROM 
(SELECT tt.* ,
@rank:= CASE WHEN @group = user_id THEN @rank + 1 ELSE 1 END rank,
@group:= tt.user_id
FROM
(SELECT 
  id,
  user_id,
  category_id,
  post_id 
FROM
  table_name 
GROUP BY user_id,
  category_id
  ORDER BY user_id,
  category_id 
  ) tt
 JOIN (SELECT @rank:=0,@group:=0) t1
 ) new_t
 WHERE rank <=2 /* will give 2 records per user change to 3 if you need to show 3 records per user */ 

Demo 2 Rows Per User

Demo 3 Rows Per User