MySQL uses group by grouping to implement the first N records

MySQL GROUP by group takes the first N records implementation

MySQL group, Fetch Records

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

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

Results:

Method One:

SELECT A.id,a.sname,a.clsno,a.score from AA a left JOIN AA B on 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

　　

Disassembly Analysis:

Left joins AA B on A.clsno=b.clsno and A.score<b.score

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

GROUP BY A.id,a.sname,a.clsno,a.score have Count (b.id) <2
A.id,a.sname,a.clsno,a.score can represent a student (in groups of students), if Count (b.id) <2 (no more than 2 people), then only the first second is left.

Method Two:

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 each record, judge the same class, greater than the current grade of students is not less than 2 people.

　

MySQL uses group by grouping to implement the first N records