在两列上按每个用户组选择3条记录 [英] SELECT 3 records per user group by on two columns

问题描述

我被困在一个复杂的MySQL查询中. 这是我的桌子:

I've been stuck in a complex MySQL query. Here is my table:

+--------------------------------------+
| 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，如以下结果集所示:

Now what I wish is for each user_id I want 2 rows of data but each row should have a different category_id, like the below resultset:

+--------------------------------------+
| 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.这是我的查询:

The query I've used so far using GROUP BY clause only manages to return a single row for each group, but I want 2 or possibly 3. Here is my query:

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;

请提出从这里出发的方法...

Please suggest how to go from here...

推荐答案

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

You can use user-defined variables to give rank for the same user group and in outer query you can simple use your condition for showing 2 categories per user or 3 or more as you need to

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 */ 

这篇关于在两列上按每个用户组选择3条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！