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

Home

Database

Mysql Tutorial

How to get the Nth record in each group in Mysql

Nov 22, 2016 am 10:47 AM

mysql

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.

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn