Query the first few records of each group after a group

When using MySQL, you often need to query the first few records of each group (query the first few records of each group after the group). Below is a simple example to illustrate how to write SQL statements. A simple table is designed as follows, which requires the first two pieces of data in the top ranking of the total score of each class. The test table statement is as follows: createtabletest (idintunsignednotnullauto_inc

When using MySQL, you often need to query the first few records of each group (query the first few records of each group after the group). Below is a simple example to illustrate how to write SQL statements. A simple table is designed as follows, which requires the first two pieces of data in the top ranking of the total score of each class. The test table statement is as follows: create table test (id int unsigned not null auto_inc

When using MySQL, you often need to query the first few records of each group (query the first few records of each group after the group). Below is a simple example to illustrate how to write SQL statements. A simple table is designed as follows, which requires the first two pieces of data in the top ranking of the total score of each class.

The test table statement is as follows:

create table test(id int unsigned not null auto_increment primary key,name varchar(10),class varchar(20),score varchar(20));insert into test(name, class, score) values ('gonn', '6(1)', '299');insert into test(name, class, score) values ('yyun', '6(1)', '259');insert into test(name, class, score) values ('lin', '6(1)', '289');insert into test(name, class, score) values ('mei', '6(1)', '277');insert into test(name, class, score) values ('xj', '6(2)', '287');insert into test(name, class, score) values ('zhl', '6(2)', '277');insert into test(name, class, score) values ('lwjs', '6(2)', '257');insert into test(name, class, score) values ('lulu', '6(2)', '265');

Run the preceding SQL statement to obtain the following table structure:

mysql> SELECT * FROM test;+----+------+-------+-------+| id | name | class | score |+----+------+-------+-------+|  1 | gonn | 6(1)  | 299   ||  2 | yyun | 6(1)  | 259   ||  3 | lin  | 6(1)  | 289   ||  4 | mei  | 6(1)  | 277   ||  5 | xj   | 6(2)  | 287   ||  6 | zhl  | 6(2)  | 277   ||  7 | lwjs | 6(2)  | 257   ||  8 | lulu | 6(2)  | 265   |+----+------+-------+-------+8 rows in set

Method 1

mysql> SELECT a.id,a.name,a.class,a.scoreFROM test a LEFT JOIN test b on a.class = b.class and a.score < b.scoreGROUP BY a.id,a.name,a.class,a.scoreHAVING count(b.id) < 2ORDER BY a.class,a.score DESC;+----+------+-------+-------+| id | name | class | score |+----+------+-------+-------+|  1 | gonn | 6(1)  | 299   ||  3 | lin  | 6(1)  | 289   ||  5 | xj   | 6(2)  | 287   ||  6 | zhl  | 6(2)  | 277   |+----+------+-------+-------+4 rows in set

Method 2

mysql> SELECT * FROM test aWHERE 2 >(SELECT count(*) FROM test WHERE class = a.class and score>a.score)ORDER BY a.class,a.score DESC;+----+------+-------+-------+| id | name | class | score |+----+------+-------+-------+|  1 | gonn | 6(1)  | 299   ||  3 | lin  | 6(1)  | 289   ||  5 | xj   | 6(2)  | 287   ||  6 | zhl  | 6(2)  | 277   |+----+------+-------+-------+4 rows in set

Here lists the implementation of a variety of SQL statements, some are unique to MySQL (Limit, other databases can be changed according to the actual, such as oracle rownum, ms SQL SERVER top ,..), sometimes it is supported by SQL standards. However, the efficiency may be different from the application scenarios. You can select an index based on the actual table record.