MySQL 编号及排序问题

Posted on

本文主要是介绍 MySQL 输出结果时的 Record 的序号问题，及排序问题 包括 严格递增， 重复不连续， 重复连续等。

1. 准备工作

我们先设定一个应用场景, 某贪吃蛇小游戏，每个用户信息包括 id 和昵称, id 唯一。
每结束一局，生成一条记录， 包括记录id， 完成时间，用户 id， 得分。

首先创建用户表 t_user, 并插入测试数据：

1
2
3
4
5
6
7
create table t_user(
  id int(11) unsigned not null auto_increment,
  u_name varchar(32) not null,
  primary key (`id`)
);
insert into t_user(u_name) values ("user1"),("user2"),("user3"),("user4");

成绩表 t_snake_score， 并插入测试数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
create table t_snake_score(
     id int(11) unsigned not null auto_increment,
     created_at int(11) not null,
     user_id int(11) unsigned not null,
     score int(11) unsigned not null,
     primary key (`id`),
     key `score_index`(`score`)
);
insert into t_snake_score(created_at, user_id, score) values
(1484894869, 1, 80), (1484894870, 1, 50),(1484894871, 1, 80),
(1484894872, 2, 80),(1484894873, 2, 80),(1484894874, 3, 60),
(1484894880, 1, 60),(1484894890, 3, 90),(1484894895, 4, 95);

2. 正式工作

1. 将结果按照 score 从大到小, created_at 从小到大排序, 并生成排序

以下为 3 种序号方式： 

1. 严格递增: 1,2,3,4,...
2. 重复不连续: 1,2,2,4,...
3. 重复连续: 1,2,2,3,...

对应 MySQL 语句

1
2
3
4
5
6
7
8
9
10
11
12
13
select * 
	from (
	  select AA.id, AA.created_at, AA.user_id, AA.score, 
  	    (@rank1:=@rank1+1) rank1,
	    (@rank2:=IF(@curscore=score, @rank2, @rank1)) rank2,	
        (@rank3:=IF(@curscore=score, @rank3, @rank3+1)) rank3,
        (@curscore:= score) newscore 
    from (
      SELECT id, created_at, user_id, score 
      FROM t_snake_score order by score desc, created_at asc
    ) AA 
    cross join (select @rank1:=0, @rank2:=0, @rank3 := 0, @curscore:=0) as params 
) as A;

查询结果如下：

1
2
3
4
5
6
7
8
9
10
# id, created_at, user_id, score, rank1, rank2, rank3, newscore
9, 1484894895, 4, 95, 1, 1, 1, 95
8, 1484894890, 3, 90, 2, 2, 2, 90
1, 1484894869, 1, 80, 3, 3, 3, 80
3, 1484894871, 1, 80, 4, 3, 3, 80
4, 1484894872, 2, 80, 5, 3, 3, 80
5, 1484894873, 2, 80, 6, 3, 3, 80
6, 1484894874, 3, 60, 7, 7, 4, 60
7, 1484894880, 1, 60, 8, 7, 4, 60
2, 1484894870, 1, 50, 9, 9, 5, 50

2. 找到每个 user_id 的最佳表现，如果相同，选择最先（created_at）的一个

对应 MySQL 语句

1
2
3
4
5
6
7
8
select t1.* 
from t_snake_score t1 
left join t_snake_score t2 
on t1.user_id = t2.user_id and 
	(t1.score < t2.score or 
		(t1.score = t2.score and t1.created_at > t2.created_at)
	) 
where t2.score is null;

查询结果如下：

1
2
3
4
5
# id, created_at, user_id, score
1, 1484894869, 1, 80
4, 1484894872, 2, 80
8, 1484894890, 3, 90
9, 1484894895, 4, 95

3. 找到每个 user_id 的最佳 top N 表现，如果user 的 第 N 个有相同，选择最先（created_at）的一个

假设 N = 2, 对应 MySQL 语句

1
2
3
4
5
6
7
8
select * from 
	(select id, created_at, user_id, score,
		(@rn := if(@uid = user_id, @rn + 1, if(@uid := uid, 1, 1) )) as seqnum
	from t_snake_score 
	cross join (select @rn := 0, @uid := "") as params 
	order by user_id asc, score desc, created_at asc
) as A where seqnum <= 2;

查询结果如下：

1
2
3
4
5
6
7
8
# id, created_at, user_id, score, seqnum
1, 1484894869, 1, 80, 1
3, 1484894871, 1, 80, 2
4, 1484894872, 2, 80, 1
5, 1484894873, 2, 80, 2
8, 1484894890, 3, 90, 1
6, 1484894874, 3, 60, 2
9, 1484894895, 4, 95, 1