mysql分组排名

1.创建表并写入测试数据

create table tb_rank_score

(

city varchar(20),

score int

);

insert into tb_rank_score values('sz',89);

insert into tb_rank_score values('sz',76);

insert into tb_rank_score values('sz',50);

insert into tb_rank_score values('sz',89);

insert into tb_rank_score values('sz',78);

insert into tb_rank_score values('sh',90);

insert into tb_rank_score values('sh',34);

insert into tb_rank_score values('sh',78);

insert into tb_rank_score values('sh',67);

insert into tb_rank_score values('sh',76);

2.普通排名:从1开始，顺序往下排

SELECT tb_rank_score.*,@r :=@r + 1 AS rank
FROM tb_rank_score,(SELECT @r := 0) r
ORDER BY score desc;