MySQL implementation row_number () grouping sorting function

Database Basic Script

CREATE TABLE ' A ' (' ID ' int () null default NULL, ' class ' int (TEN) null default NULL, ' score ' int (TEN) null default NULL) COLLA Te= ' Utf8_general_ci ' Engine=innodb;insert into a values (1,1,110); insert into a values (2,1,120); insert into a values (3,1, Insert into a values (4,1,140), insert into a values (5,2,210), insert into a values (6,2,220), insert into a values (7, 2,230) insert into a values (8,2,240), insert into a values (9,3,310), insert into a values (10,3,320), insert into a values (11,4,410);

1. Group class to take the first 2 of each group

Select Id,class,score,rank from (select B.id,b.class,b.score, @rownum: [Email protected]+1, if (@pdept =b.class, @rank: [ Email protected]+1, @rank: =1) as rank, @pdept: =b.classfrom (select Id,class,score from a order by ID) b, (select @rownum: = 0, @pdept: = null, @rank: =0) c) resulthaving rank <3;

Rank <3 refers to the month ruwnum<3 each group is automatically given the serial number. How many records are numbered. refers to each group.

2. The class is grouped to record two days, that is, rownum=2 data

Select Id,class,score,rank from (select B.id,b.class,b.score, @rownum: [Email protected]+1, if (@pdept =b.class, @rank: [ Email protected]+1, @rank: =1) as rank, @pdept: =b.classfrom (select Id,class,score from a order by score Desc) b, (select @r Ownum: =0, @pdept: = null, @rank: =0) c) resulthaving rank = 2;

　　

MySQL implementation row_number () grouping sorting function