Hexo
0%

mysql实现分组排序功能row_number

Posted on Edited on In

今天遇到了一个需求,要求提取若干个省市的数据,但是只展示每个省市的前五条,省市不固定,数量不固定

最后发现一个类似row_number的功能

创建表

1
2
3
4
5
6
7
CREATE TABLE `row_test` (
	`ID` INT(10) NULL DEFAULT NULL,
	`class` INT(10) NULL DEFAULT NULL,
	`score` INT(10) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

制造数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
insert into row_test values (1,1,80);
insert into row_test values (2,1,80);
insert into row_test values (3,1,80);
insert into row_test values (4,1,80);
insert into row_test values (5,2,80);
insert into row_test values (6,2,80);
insert into row_test values (7,2,80);
insert into row_test values (8,2,80);
insert into row_test values (9,2,80);
insert into row_test values (10,2,80);
insert into row_test values (11,3,80);
insert into row_test values (12,3,80);
insert into row_test values (13,3,80);
insert into row_test values (14,3,80);
insert into row_test values (15,3,80);
insert into row_test values (16,4,80);
insert into row_test values (17,4,80);
insert into row_test values (18,4,80);
insert into row_test values (19,4,80);
insert into row_test values (20,4,80);

对class进行分组(模拟省市),取每个class的前三条数据

1
2
3
4
5
6
7
8
select id,class,score,rank from (
select b.id,b.class,b.score,@rownum:=@rownum+1 ,
if(@pdept=b.class,@rank:=@rank+1,@rank:=1) as rank,
@pdept:=b.class
from (
select id,class,score from row_test order by id 
) b ,(select @rownum :=0 , @pdept := null ,@rank:=0) c ) result
having rank <4 ;

完美实现,如果存在联表,需要根据class数量加上limit。