MySQL takes the first n records of each group

First, the record of the group to take the top N records: Example: Take the first 2 maximum (small) records

1 1. Using subqueries:2SELECT * from Right2 a WHERE2>3(SELECT COUNT (*) from right2 b WHERE b.id=a.id and b.account>a.account)4 ORDER by A.id,a.account DESC5 2. With exists half connection:6SELECT *From right2 a WHERE EXISTS7(SELECT count (*) from right2 b WHERE b.id=a.id and A.account<b.account have COUNT (*) <2)8 ORDER by A.id,a.account DESC9 Similarly, you can take the smallest n records in a group:TenSELECT * from Right2 a WHERE2> One(SELECT COUNT (*) from right2 b WHERE b.id=a.id and b.account<a.account) A ORDER by A.id,a.account DESC - with exists: -SELECT *From right2 a WHERE EXISTS the(SELECT count (*) from right2 b WHERE b.id=a.id and A.account>b.account have COUNT (*) <2) -ORDER by A.id,a.account DESC

If you take the maximum (small) one record for each group I often use:

1 Select  from T-B inner JOIN (selectfromwhere  order by Val Desc) A on  a.id=b.id Group B Y a.id ORDER by  ID;

Two. Example: Take the top N of each group

1 CREATE TABLE T2 (2Idintprimary KEY,3GidChar, 4Col1int, 5Col2int  6) engine=InnoDB;7 INSERT INTO tx01 values8(1,'A', to,6), 9(2,'B', -, the), Ten(3,'C', the, +),  One(4,'D', the, About),  A(5,'E',3, -),  -(6,'A', in, the),  -(7,'B', the, the),  the(8,'C', -, A),  -(9,'D', -, +),  -(Ten,'E', $, -),  -( One,'A',2, +),  +( A,'B', -, -),  -( -,'C', the, the),  +( -,'D',Panax Notoginseng, +),  A( the,'E', -, the),  at( -,'A', +, the),  -( -,'B', -, -),  -( -,'C', -,9),  -( +,'D', to, +),  -( -,'E', the, the),  -( +,'A', the,9),  in( A,'B', the, A),  -( at,'C', $, -),  to( -,'D', Wu, -),  +( -,'E', -, -),  -( -,'A', the, -),  the( -,'B', +, $),  *( -,'C', the, -),  $( in,'D', the,8), Panax Notoginseng( -,'E', the, +),  -( to,'A', the, the),  the( +,'B',4, -),  +( -,'C',Bayi,7),  A( the,'D', One, A),  the( *,'E', -,Ten),  +( $,'A', the, the),  -(Panax Notoginseng,'B', A, the),  $( -,'C', the, the),  $( the,'D', -, One),  -( +,'E',Bayi, $);  - CREATE TABLE tx01 ( theIdintprimary KEY, -GidChar, WuyiCol1int,  theCol2int   -) Engine=innodb;

Maximum first n records per group of GID: using self- connected or semi-connected :

When *n=1:

Self-connect: After descending, group by takes the largest of each group.

Select  from (Select from T2 ORDER BY col2 Desc)  as a group by GID order by GID;

Half-connection: No larger than the maximum value can be found.

Select  from where NOT EXISTS (select1fromwhere

When *n=3:

Self-connect:

Select  from where 3> (selectfromwhere Gid=a.gid and col2>a.col2) Order by a.gid,a.col2 Desc;

Half Connection:

Select  from where exists (selectfromwhere B.gid=a.gid and A.col2<b.col2 have ( COUNT (*)) <3) Order BY a.gid,a.col2 Desc

Turn: This article is from http://huanghualiang.blog.51cto.com/6782683/1252630

MySQL takes the first n records of each group