mysql 每组 最大2条,MySQL每组前2条记录

Basically I need to get only the last 2 records for each user, considering the last created_datetime:

id | user_id | created_datetime

1 | 34 | '2015-09-10'

2 | 34 | '2015-10-11'

3 | 34 | '2015-05-23'

4 | 34 | '2015-09-13'

5 | 159 | '2015-10-01'

6 | 159 | '2015-10-02'

7 | 159 | '2015-10-03'

8 | 159 | '2015-10-06'

Returns (expected output):

2 | 34 | '2015-10-11'

1 | 34 | '2015-09-10'

7 | 159 | '2015-10-03'

8 | 159 | '2015-10-06'

I was trying with this idea:

select user_id, created_datetime,

$num := if($user_id = user_id, $num + 1, 1) as row_number,

$id := user_id as dummy

from logs group by user_id

having row_number <= 2

The idea is keep only these top 2 rows and remove all the others.

Any ideas?

解决方案

Your idea is close. I think this will work better:

select u.*

from (select user_id, created_datetime,

$num := if(@user_id = user_id, @num + 1,

if(@user_id := id, 1, 1)

) as row_number

from logs cross join

(select @user_id := 0, @num := 0) params

order by user_id

) u

where row_number <= 2 ;

Here are the changes:

The variables are set in only one expression. MySQL does not guarantee the order of evaluation of expressions, so this is important.

The work is done in a subquery, which is then processed in the outer query.

The subquery uses order by, not group by.

The outer query uses where instead of having (actually, in MySQL having would work, but where is more appropriate).