Today, someone in a group asked a question: how to execute a query in msql: retrieve data from a table, group data by a field, and then retrieve the third data in each group. Some people talk about the implementation of Oracle and use rank. Of course, I don't understand oracle. Google's result is that there is no rank function in MySQL. Then I found an articleArticleAfter studying it, I finally made a hard move. Below is a link to the reference article: http://blog.sina.com.cn/s/blog_4d18beb10100y3kt.html
Create a table as an example:
1 Create Table Sam ( 2 'A' Int ( 11 ) Default Null , 3 'B' Int ( 11 ) Default Null 4 ) Engine = MyISAM Default Charset = Utf8; 5 Insert Into Sam Values (1 , 10 ),( 1 , 15 ),( 1 , 20 ),( 1 , 25 ),( 2 , 20 ),( 2 ,22 ),( 2 , 33 ),( 2 , 45 );
The data in the table is as follows:
A B
110
115
120
125
220
222
233
245
Then there is a query that adds the serial number to each group of rows:
1 Select A, B, 2 @ Rank : = If ( @ = A, @ Rank + 1 , 1 ) Rank, 3 @ : = A 4 From 'Sam ', 5 (Select @ Rank : = 1 , @ : = Null ) Tt;
Query results:
A B rank @ A: =
11011
11521
12031
12541
22012
22222
23332
24542
There are some points to explain here.
Row 1st: User variables and if functions are used here. User variables are temporary variables for each query, similar to the temporary variables in the Java method. The IF function is similar to the IF statement, which contains three parameters. The first is the condition, the second is the return value when the condition is successful, and the third is the return value when the condition fails.
Row 3rd: Save the value of column A to compare the value of column A with the value of column A in the IF column to determine whether rank needs to be reset to 1.
In row 3, nothing is actually done here. The main function is similar to declaring variables. It will be very strange to not declaring the results anyway. The detailed reasons have not been studied clearly.
To sum up, the query statement for the third row in each group is:
1 Select T. A, T. B From ( Select A, B, @ Rank : = If ( @ = A, @ Rank + 1 , 1 ) Rank,@ : = A From Sam) T, 2 ( Select @ Rank : = 1 , @ : = Null ) TT Where Rank = 3 ;
Query results:
A B
120
233
The sequence is not considered above, because the raw data is originally ordered. If you consider the order of A and B and the possible duplicate values of B, you can use the following statement to insert several duplicate data entries for testing:
Select T. A, T. B From ( Select F. A, F. B, @ Rank : = If ( @ B <> F. B, If ( @ = F., @ Rank + 1 , 1 ), @ Rank ) Rank, @ : = F., @ B : = F. B From ( Select A, BFrom 'Sam' Order By A, B) F ,( Select @ Rank : = 1 , @ : = Null , @ B : = Null ) TT) T Where Rank= 3 ;