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

code is as follows

copy code

mysql> SELECT * from T_subject; | id | uid | subject | | 1 | 1 | aa | | 2 | 2 | bb | | 3 | 3 | cc | | 4 | 4 | dd | | 5 | 2 | ee | | 6 | 2 | rr | | 7 | 3 | tt | | 8 | 2 | yy | | 9 | 3 | qq | | 4 | oo | | | 3 | pp | | 3 | kk | | 1 | mm | | 4 | nn | | 1 | ss | | 4 | vv | | 1 | ff | Select UID, group_concat (subject) from (select ID, uid, subject from (select ID, uid, subject, (select Cou NT (*) from T_subject where uid = T.uid and subject <= T.subject) RK from T_subject t) T1 where R K <= 3) T2 GROUP by uid   | uid | group_concat (a.subject) | | 1 | aa,ff,ss,mm | | 2 | yy , RR,EE,BB | | 3 | kk,pp,xx,qq,tt,cc | | 4 | nn,dd,vv,oo |

----What I want to do is to write the following effect (that is, remove the first three records of each group after grouping)---

The code is as follows	Copy Code
| UID | Group_concat (A.subject) | | 1 | aa,ff,mm | | 2 | Yy,rr,ee | | 3 | kk,pp,xx | | 4 | NN,DD,VV |

Example Two

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:

The code is as follows	Copy Code
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:

The code is as follows	Copy Code
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:

The code is as follows	Copy Code
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.