mysql - 从表中选择每个用户的前 3 个查看图像

Question

我知道此类问题已被多次问到，但我找不到适合我的问题的答案。我试图从表中获取每个人的前 3 条查看记录。

我的表

id    user_id     views
1       5          6
2       5          3
3       6          5  
4       7          4  
5       9          14  
6       5          3
7       5          8
8       6          7
9       7         15
10      9         13
11      9         13  
12      9         18
13      6          8
14      8          0

结果

id      user_id    views
12       9          18
9        7          15
5        9          14
10       9          13
13       6          8
7        5          8
8        6          7
1        5          6
3        6          5
4        7          4
2        5          3
14       8          0

我点击了这个链接 http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

我的查询

select *
from mytable
where (
   select count(*) from mytable as f
   where f.user_id = mytable.user_id and f.views >= mytable.views
) < 4 order by user_id desc;

但是这个查询并没有获取那些 View 相等的记录。请指出正确的方向。

Answer 1

试试这个:

SELECT id, user_id, views 
FROM (SELECT IF(@lastUserId = @lastUserId:=t.user_id, @ID:=@ID+1, @ID:=1) AS userNo, 
                 t.id, t.user_id, t.views
        FROM mytable t, (SELECT @ID:=0, @lastUserId:=0) A 
        ORDER BY t.user_id, t.views DESC
      ) AS A 
WHERE userNo <= 3 
ORDER BY views DESC

检查 SQL FIDDLE DEMO

输出

| ID | USER_ID | VIEWS |
|----|---------|-------|
| 12 |       9 |    18 |
|  9 |       7 |    15 |
|  5 |       9 |    14 |
| 10 |       9 |    13 |
|  7 |       5 |     8 |
| 13 |       6 |     8 |
|  8 |       6 |     7 |
|  1 |       5 |     6 |
|  3 |       6 |     5 |
|  4 |       7 |     4 |
|  2 |       5 |     3 |
| 14 |       8 |     0 |