mysql - 使用分组表获取前 n 条记录

Question

我有一个这样的订单表：

|---------------------|------------------|
|      created_at     |     user_id      |
|---------------------|------------------|
| 2019-10-14 01:01:59 |       1          |
|---------------------|------------------|
| 2019-10-14 02:01:59 |       2          |
|---------------------|------------------|
| 2019-10-13 00:01:59 |       1          |
|---------------------|------------------|
| 2019-10-14 03:01:59 |       3          |
|---------------------|------------------|
| 2019-10-12 23:01:59 |       2          |
|---------------------|------------------|
| 2019-10-12 23:01:59 |       1          |
|---------------------|------------------|
| 2019-10-10 14:01:59 |       3          |
|---------------------|------------------|
| 2019-10-11 20:01:59 |       2          |
|---------------------|------------------|
| 2019-10-09 12:01:59 |       3          |
|---------------------|------------------|

我按此查询对表进行分组，以获得每个用户的最新订单：

select created_at, user_id
from orders
order by user_id, created_at desc

我得到了这些记录：

|---------------------|------------------|
|      created_at     |     user_id      |
|---------------------|------------------|
| 2019-10-14 01:01:59 |       1          |
|---------------------|------------------|
| 2019-10-13 00:01:59 |       1          |
|---------------------|------------------|
| 2019-10-12 23:01:59 |       1          |
|---------------------|------------------|
| 2019-10-14 02:01:59 |       2          |
|---------------------|------------------|
| 2019-10-12 23:01:59 |       2          |
|---------------------|------------------|
| 2019-10-11 20:01:59 |       2          |
|---------------------|------------------|
| 2019-10-14 03:01:59 |       3          |
|---------------------|------------------|
| 2019-10-10 14:01:59 |       3          |
|---------------------|------------------|
| 2019-10-09 12:01:59 |       3          |
|---------------------|------------------|

现在我希望它每组只返回 2 个第一条记录。我怎么能这样做？

谢谢！

标签： mysql

Answer 1

如果您使用的是mysql 8.0或更高版本，则可以使用row_number() over partition by..

select t1.created_at, t1.user_id from (
select row_number() over (partition by user_id order by created_at desc) rn, created_at, user_id
from orders) t1 where t1.rn <=2

使用mysql 5.7及以下版本

SELECT t1.created_at, t1.user_id 
FROM (SELECT 
      @row_number:=CASE
          WHEN @varId = user_id 
            THEN 
                @row_number + 1
            ELSE 
                 1
          END AS rn,
      @varId:=iuser_id user_id,
      created_at
    FROM
        orders,
        (SELECT @varId:=0,@row_number:=0) as t
    ORDER BY 
        user_id asc, created_at desc) t1 
WHERE t1.rn <= 2