Mysql uses GROUP by group to achieve the first N records method _mysql

This article illustrates how MySQL uses the group by group to achieve the first N records. Share to everyone for your reference, specific as follows:

In MySQL, the group by group takes the first N records to realize

MySQL grouping, taking records

How to take the top two bits of each group by following I'll tell you how to implement the first N records in MySQL by grouping by group.

This is the test table (also do not know how to think, when the table name directly knocked a AA, Khan ~ ~ ~ ~ ~ ~ ~):

Results:

Method One:

Copy Code code as follows:

SELECT A.id,a.sname,a.clsno,a.score from AA a left JOIN AA B in A.clsno=b.clsno and A.score<b.score GROUP by A.id,a.sna Me,a.clsno,a.score has count (b.id) <2 ORDER by a.clsno,a.score Desc;

To disassemble the analysis:

<!--[if!supportlists]-->1, <!--[Endif]-->left JOIN AA B on A.clsno=b.clsno and A.score<b.score

The same class (four in each class), the score is higher than the current record of students, which means that the bottom of the students, will produce three records

<!--[if!supportlists]-->2, <!--[Endif]-->group by A.id,a.sname,a.clsno,a.score has count (b.id) <2
A.id,a.sname,a.clsno,a.score can represent a student (grouped by student), and if Count (b.id) <2 (more than 2 people can score more than you), then there is only the first second.

Method Two:

Copy Code code as follows:

SELECT * from AA a WHERE 2> (select COUNT (*) from AA where Clsno=a.clsno and Score>a.score) Order by A.clsno,a.score DESC;

This I think is more interesting, take every record, judge the same class, more than the current score of students is not less than 2 people.

Method Three:

Copy Code code as follows:

SELECT * from AA where ID in (select ID from AA where Clsno=a.clsno order by Score DESC LIMIT 2) Order by A.clsno,a.score DESC;

This way went through the test does not pass, ERROR 1235 (42000): This version of the MySQL doesn ' t yet support ' LIMIT & In/all/any/some ', can not be found in these several sub Use limit in your inquiries.

More information about MySQL interested readers can view the site topics: "MySQL Log operation skills Daquan", "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and "MySQL commonly used function large summary"

I hope this article will help you with the MySQL database meter.