How to get the Nth record in each group in Mysql-Mysql Tutorial-php.cn

How to get the Nth record in each group in Mysql

高洛峰

Release： 2016-11-22 10:47:24

Original

1280 people have browsed it

Problem background

1) Restrictions: Each user can enjoy up to 4 free discounts for orders in one category.

That is, when counting the number of orders that have enjoyed the free shipping discount, orders placed after the fourth order in the same category need to be excluded.

How to get the ID of the fourth order in the same category for each user?

select fourth(id) from order group by user_id, category;

Copy after login

Unfortunately, Mysql does not have a grouping function to easily get the fourth id in each group. But there can be workarounds, such as the following table

select * from t;
+----+--------+----------------------------------+
| id | status | user_id                          |
+----+--------+----------------------------------+
|  1 | 10     | 24e568a88fae11e6bb0650b497d97cdd |
|  2 | 10     | 24e568a88fae11e6bb0650b497d97cdd |
|  3 | 20     | 24e568a88fae11e6bb0650b497d97cdd |
|  4 | 20     | 24e568a88fae11e6bb0650b497d97cdd |
|  5 | 10     | e8669ac28fae11e6bb0650b497d97cdd |
|  6 | 20     | e8669ac28fae11e6bb0650b497d97cdd |
|  7 | 10     | e8669ac28fae11e6bb0650b497d97cdd |
|  8 | 20     | e8669ac28fae11e6bb0650b497d97cdd |
|  9 | 10     | e8669ac28fae11e6bb0650b497d97cdd |
+----+--------+----------------------------------+

Copy after login

How to get the order of each user's second successful payment (status='10')?

How to get the Nth record in each group in Mysql

Method 1 - Query

select * from t where user_id = &#39;24e568a88fae11e6bb0650b497d97cdd&#39; and status = &#39;10&#39; order by id limit 1,1;
+----+--------+----------------------------------+
| id | status | user_id                          |
+----+--------+----------------------------------+
|  2 | 10     | 24e568a88fae11e6bb0650b497d97cdd |
+----+--------+----------------------------------+

Copy after login

one by one

Option 2 - One-time query

SELECT 
    *
FROM
    (SELECT 
        id,
            user_id,
            @rank:=IF(@current_user_id = user_id, @rank + 1, 1) rank,
            @current_user_id:=user_id
    FROM
        (SELECT @current_user_id:=NULL, @rank:=NULL) vars, t
    WHERE
        t.status = &#39;10&#39;
    ORDER BY user_id , id) a
WHERE
    rank = 2;
    
+----+----------------------------------+------+----------------------------------+
| id | user_id                          | rank | @current_user_id:=user_id        |
+----+----------------------------------+------+----------------------------------+
|  2 | 24e568a88fae11e6bb0650b497d97cdd |    2 | 24e568a88fae11e6bb0650b497d97cdd |
|  7 | e8669ac28fae11e6bb0650b497d97cdd |    2 | e8669ac28fae11e6bb0650b497d97cdd |

Copy after login

Explanation of principle

First sort by user_id and id, and then assign serial numbers starting from 1, the same user will be incremented, different users Then start again from 1.