简单加入，每组最多？

问题描述：

USERS: 
userid | name 
1  | John 
2  | Jack 

HITS: 
id | userid | time 
1 | 1  | 50 
2 | 1  | 51 
3 | 2  | 52 
4 | 1  | 53 
5 | 2  | 54 
6 | 2  | 55

我怎样才能得到每个用户的前两名命中。换句话说，修改此：

select 
    users.userid, 
    h.time 
from 
    users 
left outer join hits h 
on (users.userid = h.userid)

得到这个：

array() { 
    [user 1] { 
    hit 1 => 50 
    hit 2 => 51 
    } 
    [user 2] { 
    hit 3 => 52 
    hit 5 => 54 
    } 
}

？

我知道一个正常的连接会给我所有的东西，但是我怎样才能将它限制为只有2个点击，从而有效地获得每个用户的两个点击量？

答

尝试此查询 -

SELECT t.id, t.userid, t.time FROM (
    SELECT h1.*, COUNT(*) pos FROM hits h1 
    LEFT JOIN hits h2 
     ON h2.userid = h1.userid AND h2.time <= h1.time 
    GROUP BY 
    h1.userid, h1.time) t 
WHERE pos <= 2;

答

SELECT a.userid, 
     a.name, 
     b.time 
FROM users a 
     INNER JOIN hits b 
      ON a.userid = b.userid 
WHERE 
    (
     SELECT COUNT(*) 
     FROM hits as c 
     WHERE b.userid = c.userid and 
       b.id >= c.id 
    ) <= 2;

SQLFiddle Demo

答

尝试此查询

SET @level = 0; 
SET @group = ''; 

SELECT 
* 
FROM (
    SELECT 
    time, 
    userid, 
    @level := IF(@group = userid, @level+1, 1) AS level, 
    @group := userid as UGroup 
    FROM hits 
    ORDER BY userid 
) rs 
WHERE level < 3

Demo

简单加入，每组最多？

相关推荐