Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2849935
  • 博文数量: 718
  • 博客积分: 8199
  • 博客等级: 中将
  • 技术积分: 10344
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-25 20:19
个人简介

脚踏实地、勇往直前!

文章分类

全部博文(718)

文章存档

2018年(66)

2017年(75)

2016年(48)

2015年(48)

2014年(152)

2013年(49)

2012年(157)

2011年(93)

2010年(30)

分类: Mysql/postgreSQL

2018-06-11 20:16:29


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开始,顺序往下排
mysql> SELECT tb_rank_score.*,@r :=@r + 1 AS rank
    -> FROM tb_rank_score,(SELECT @r := 0) r
    -> ORDER BY score desc;
+------+-------+------+
| city | score | rank |
+------+-------+------+
| sh   |    90 |    1 |
| sz   |    89 |    2 |
| sz   |    89 |    3 |
| sz   |    78 |    4 |
| sh   |    78 |    5 |
| sz   |    76 |    6 |
| sh   |    76 |    7 |
| sh   |    67 |    8 |
| sz   |    50 |    9 |
| sh   |    34 |   10 |
+------+-------+------+
10 rows in set (0.00 sec)


3.并列排名:相同的值是相同的排名
mysql> SELECT tb_rank_score.* ,
    -> CASE 
    -> WHEN @p=score THEN @r
    -> WHEN @p:=score THEN @r:=@r+1
    -> END rank
    -> FROM tb_rank_score,(SELECT @r:=0,@p:=NULL)r
    -> ORDER BY score desc;
+------+-------+------+
| city | score | rank |
+------+-------+------+
| sh   |    90 |    1 |
| sz   |    89 |    2 |
| sz   |    89 |    2 |
| sz   |    78 |    3 |
| sh   |    78 |    3 |
| sz   |    76 |    4 |
| sh   |    76 |    4 |
| sh   |    67 |    5 |
| sz   |    50 |    6 |
| sh   |    34 |    7 |
+------+-------+------+
10 rows in set (0.00 sec)


4.分组后组内排名
mysql> SELECT city,score,rank
    -> FROM
    -> (
    -> SELECT tb_rank_score.*,IF(@p=city,@r:=@r+1,@r:=1) AS rank,
    -> @p:=city
    -> FROM tb_rank_score,(SELECT @p:=NULL,@r:=0)r
    -> ORDER BY city,score desc
    -> )s;
+------+-------+------+
| city | score | rank |
+------+-------+------+
| sh   |    90 |    1 |
| sh   |    78 |    2 |
| sh   |    76 |    3 |
| sh   |    67 |    4 |
| sh   |    34 |    5 |
| sz   |    89 |    1 |
| sz   |    89 |    2 |
| sz   |    78 |    3 |
| sz   |    76 |    4 |
| sz   |    50 |    5 |
+------+-------+------+
10 rows in set (0.00 sec)


5.分组后并列排名:组内相同数值排名相同
mysql> SELECT city,score,rank
    -> FROM
    -> (
    -> SELECT *,
    -> IF(@p=city,
    ->     CASE 
    ->        WHEN @s=score THEN @r
    ->        WHEN @s:=score THEN @r:=@r+1
    ->     END,
    ->    @r:=1 ) AS rank,
    -> @p:=city,
    -> @s:=score
    -> FROM tb_rank_score,(SELECT @p:=NULL,@s:=NULL,@r:=0)r
    -> ORDER BY city,score desc
    -> )s;
+------+-------+------+
| city | score | rank |
+------+-------+------+
| sh   |    90 |    1 |
| sh   |    78 |    2 |
| sh   |    76 |    3 |
| sh   |    67 |    4 |
| sh   |    34 |    5 |
| sz   |    89 |    1 |
| sz   |    89 |    1 |
| sz   |    78 |    2 |
| sz   |    76 |    3 |
| sz   |    50 |    4 |
+------+-------+------+
10 rows in set (0.00 sec)


6.分组后取各组的前三名
mysql> SELECT city,score,rank
    -> FROM
    -> (
    -> SELECT *,
    -> IF(@p=city,
    ->     CASE 
    ->         WHEN @s=score THEN @r
    ->         WHEN @s:=score THEN @r:=@r+1
    ->     END,
    ->   @r:=1 ) AS rank,
    -> @p:=city,
    -> @s:=score
    -> FROM tb_rank_score,(SELECT @p:=NULL,@s:=NULL,@r:=0)r
    -> ORDER BY city,score DESC 
    -> )s
    -> WHERE rank <4;
+------+-------+------+
| city | score | rank |
+------+-------+------+
| sh   |    90 |    1 |
| sh   |    78 |    2 |
| sh   |    76 |    3 |
| sz   |    89 |    1 |
| sz   |    89 |    1 |
| sz   |    78 |    2 |
| sz   |    76 |    3 |
+------+-------+------+
7 rows in set (0.00 sec)



阅读(0) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册