CREATE TABLE ' Zsl_test ' (
' ID ' INT (TEN) null DEFAULT NULL,
' Class ' INT (TEN) null DEFAULT NULL,
' Score ' INT (TEN) null DEFAULT NULL
)
Collate= ' Utf8_general_ci '
Engine=innodb;
INSERT into zsl_test values (1,1,80);
INSERT into zsl_test values (2,1,80);
INSERT into zsl_test values (3,1,80);
INSERT into zsl_test values (4,1,80);
INSERT into zsl_test values (5,2,80);
INSERT into zsl_test values (6,2,80);
INSERT into zsl_test values (7,2,80);
INSERT into zsl_test values (8,2,80);
INSERT into zsl_test values (9,2,80);
INSERT into zsl_test values (10,2,80);
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.class
From (
Select Id,class,score from Zsl_test ORDER by ID
) b, (select @rownum: =0, @pdept: = null, @rank: =0) c) result
Having rank <3;
Implementing Row_number in MySQL