文档章节

用mysql语句 实现按成绩 排名次

vga
 vga
发布于 2017/03/08 06:53
字数 1567
阅读 81
收藏 0

mysql:查询排名

sql语句查询排名

思路:有点类似循环里面的自增一样,设置一个变量并赋予初始值,循环一次自增加1,从而实现排序;

   mysql里则是需要先将数据查询出来并先行按照需要排序的字段做好降序desc,或则升序asc,设置好排序的变量(初始值为0):

   a>.将已经排序好的数据从第一条依次取出来,取一条就自增加一,实现从1到最后的一个排名

   b>.当出现相同的数据时,排名保持不变,此时则需要再设置一个变量,用来记录上一条数据的值,跟当前数据的值进行对比,如果相同,则排名不变,不相同则排名自增加1

   c.当出现相同的数据时,排名保持不变,但是保持不变的排名依旧会占用一个位置,也就是类似于(1,2,2,2,5)这种排名就是属于中间的三个排名是一样的,但是第五个排名按照上面一种情况是(1,2,2,2,3),现在则是排名相同也会占据排名的位置

准备数据(用户id,分数):

CREATE TABLE sql_rank ( id int(11) unsigned NOT NULL AUTO_INCREMENT, user_id int(11) unsigned NOT NULL, score tinyint(3) unsigned NOT NULL, add_time date NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

插入数据:

INSERT INTO sql_rank (user_id, score, add_time) VALUES (100, 50, '2016-05-01'), (101, 30, '2016-05-01'), (102, 20, '2016-05-01'), (103, 60, '2016-05-01'), (104, 80, '2016-05-01'), (105, 50, '2016-05-01'), (106, 70, '2016-05-01'), (107, 85, '2016-05-01'), (108, 60, '2016-05-01')

当前数据库数据:

一、sql1{不管数据相同与否,排名依次排序(1,2,3,4,5,6,7.....)}

复制代码 SELECT obj.user_id,obj.score,@rownum := @rownum + 1 AS rownum FROM ( SELECT user_id, score FROM sql_rank ORDER BY score DESC ) AS obj, (SELECT @rownum := 0) r 复制代码 执行的结果如下图:

  

可以看到,现在按照分数从1到9都排好序了,但是有些分数相同的用户排名却不一样,这就是接下来要说的第二种sql

二、sql2{只要数据有相同的排名就一样,排名依次排序(1,2,2,3,3,4,5.....)}

复制代码 SELECT obj.user_id, obj.score, CASE WHEN @rowtotal = obj.score THEN @rownum WHEN @rowtotal := obj.score THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN @rownum :=@rownum + 1 END AS rownum FROM ( SELECT user_id, score FROM sql_rank ORDER BY score DESC ) AS obj, (SELECT @rownum := 0 ,@rowtotal := NULL) r 复制代码 这时候就新增加了一个变量,用于记录上一条数据的分数了,只要当前数据分数跟上一条数据的分数比较,相同分数的排名就不变,不相同分数的排名就加一,并且更新变量的分数值为该条数据的分数,依次比较

如下图结果:

  

跟第一条sql的结果相对比你会发现,分数相同的排名也相同,并且最后一名的名次由第9名变成了第7名;

如果你需要分数相同的排名也相同,但是后面的排名不能受到分数相同排名相同而不占位的影响,也就是哪怕你排名相同,你也占了这个位置(比如:1,2,2,4,5,5,7....这种形式的,虽然排名有相同,但是你占位了,后续的排名根据占位来排)

三、sql2{只要数据有相同的排名就一样,但是相同排名也占位,排名依次排序(1,2,2,4,5,5,7.....)}  

   此时需呀再增加一个变量,来记录排序的号码(自增)

按 Ctrl+C 复制代码

SELECT obj_new.user_id, obj_new.score, obj_new.rownum FROM ( SELECT obj.user_id, obj.score, @rownum := @rownum + 1 AS num_tmp, @incrnum := CASE WHEN @rowtotal = obj.score THEN @incrnum WHEN @rowtotal := obj.score THEN @rownum END AS rownum FROM ( SELECT user_id, score FROM sql_rank ORDER BY score DESC ) AS obj, ( SELECT @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0 ) r ) AS obj_new 按 Ctrl+C 复制代码 上面sql执行的结果如下:

  

  结果集中分数相同的,排名相同,同时它也占据了那个位置,中间的一个数据过程本人截图了,请往下看(跟上图做对比你就明白了):

  

本文参考 
http://www.cnblogs.com/zengguowang/p/5541431.html 
http://www.bubuko.com/infodetail-823289.html

USE test_db;

建表

CREATE TABLE t_rank 
( 
pid INT(4) PRIMARY KEY AUTO_INCREMENT, 
NAME VARCHAR(20) NOT NULL, 
age INT(3), 
groupid INT 
)

插入

INSERT INTO t_rank(NAME,age,groupid) 
VALUES 
(‘Peter’, 19, 1), 
(‘Andre’,20, 1), 
(‘Vino’,20, 3), 
(‘John’,25, 3), 
(‘Tom’,24, 4), 
(‘Brian’,21, 4), 
(‘Andy’,22, 3), 
(‘George’,23, 4), 
(‘Dew’,23, 4), 
(‘Kris’,25, 4), 
(‘Samual’,25, 4), 
(‘William’,26, 2);

(相同数值自动排名,没有并列)

降序排名 没有并列,相同系统自动给出序号,按照年龄排序,永远都是排名数+1 
SELECT pid, NAME, age, @curRank := @curRank + 1 AS rank 
FROM t_rank r,(SELECT @curRank := 0) t 
ORDER BY age

(相同数值相同排名)方法一

暂存上一条记录age(@preRank),如果当前age与其相等,使用与之前记录相同的排名,否则加1,更新@preRank

说明: 
1.定义当前的排名是0,前一个排名=NULL 
2.当前行排名数与前一个排名相同,输出相同排名 
3.否则,将当前行排名数赋值给前一个排名,并且当前排名数+1

SELECT pid, NAME, age, 
CASE 
WHEN @preRank = age THEN @curRank 
WHEN @preRank := age THEN @curRank := @curRank + 1 
END AS rank 
FROM t_rank r, (SELECT @curRank := 0, @preRank := NULL ) t 
ORDER BY age

相同数值,相同排名,不同数值,以前面多少个为准

SELECT pid, NAME, age, 
@rownum := @rownum + 1 AS tmp, 
@incrnum := CASE 
WHEN @rowtotal = age THEN @incrnum 
WHEN @rowtotal := age THEN @rownum 
END AS rownum 
FROM 
( 
SELECT pid, NAME, age 
FROM t_rank 
ORDER BY age DESC 
) AS a,(SELECT @rownum := 0, @rowtotal := NULL, @incrnum := 0) b

(相同数值相同排名)方法二

增加一个变量(@incRank) 用于记录序号 
1.如果当前age与前一个记录记录相同,使用前一个记录相同数 
2.否则使用序号 
说明: 
判断IF(@preRank = age, @curRank, @incRank) 
如果 当前行数据等于前一个排名就是当前排名

测试变量是否正确 
SELECT pid, NAME, age,@curRank, 
@curRank := IF(@preRank = age, @curRank, @incRank) AS rank, #最新排名值,永远复制给@curRank,并且输出 
@incRank := @incRank + 1, #自增 
@preRank := age #记录了最新上一个排名值 20 
FROM t_rank r,(SELECT @curRank := 0, @preRank := NULL, @incRank := 1) t 
ORDER BY age
2.将排名信息显示出来 
SELECT pid, NAME, age, rank 
FROM ( 
SELECT pid, NAME, age, 
@curRank := IF(@preRank = age, @curRank, @incRank) AS rank, 
@incRank := @inRank + 1, 
@preRank := age 
FROM t_rank r,(SELECT @curRank := 0, @preRank := NULL, @incRank := 1) t 
ORDER BY age 
) s

本文转载自:http://www.cnblogs.com/zengguowang/p/5541431.html

vga

vga

粉丝 23
博文 366
码字总数 26645
作品 0
佳木斯
私信 提问
加载中

评论(3)

vga
vga 博主
2、分句完成。效率高。
存储过程:
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`sp_rank`$$
CREATE PROCEDURE `test`.`sp_rank`(IN str_id int(11))
BEGIN
-- user's score
DECLARE str_score int;
-- user's rank
DECLARE rank int;
select score from scores where id = str_id order by score desc limit 1 into str_score ;
select count(*) from scores where score >=str_score into rank;
-- output
select id,score,rank from scores where id = str_id;
END$$
DELIMITER ;

mysql> call sp_rank(100);
+-----+-------+--------+
| id | score | rank |
+-----+-------+--------+
| 100 | 64 | 370311 |
+-----+-------+--------+
1 row in set (1.02 sec)
Query OK, 0 rows affected (1.02 sec)
vga
vga 博主
mysql> show create table scores\G
*************************** 1. row ***************************
Table: scores
Create Table: CREATE TABLE `scores` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`score` int(11) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `k_s` (`score`)
) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

mysql> select count(1) from scores;
+----------+
| count(1) |
+----------+
| 1000000 |
+----------+
1 row in set (0.00 sec)


mysql> select id,score,(select count(1) from scores where score>= (select score
from scores where id = 100 order by score desc limit 1)) as rank from scores whe
re id = 100;
+-----+-------+--------+
| id | score | rank |
+-----+-------+--------+
| 100 | 64 | 370311 |
+-----+-------+--------+
1 row in set (1.05 sec)
vga
vga 博主
FROM
(
SELECT A.*,@rank :=@rank+1 as pm
FROM
(
SELECT banji,avg(score) as AvgS FROM table_test GROUP BY banji ORDER BY AvgS DESC
) A ,(SELECT @rank :=0) B
) M
ORDER BY M.banji
LeetCode:Rank Scores - 按分数排名次

1、题目名称 Rank Scores(按分数排名次) 2、题目地址 https://leetcode.com/problems/rank-scores/ 3、题目内容 按分数排名次,如果两个Id的分数一样,那么他们的名次是一样的,排名从1开始...

北风其凉
2015/08/16
1K
0
SQL语句经典练习30题:基于Navicat for SQLite本地实现

本文介绍利用Navicat for SQLite,省去在本地安装数据库的繁琐环节,轻松在本地实现SQL语句的练习。本文列出的30道题目,基本涵盖全部的sql查询语句,更经典,更实用。 第一部分:环境搭建 ...

AiFan
2018/11/09
0
0
Python日志之Python控制流(顺序,分支,循环)

Python控制流: 所有程序都是在IDLE中运行 1.Python的三种控制流 (1).什么是控制流 Python中程序代码执行是有序的,有的代码程序会从上倒下按顺序执行,有的程序会跳转着执行,有的程序代码...

暗夜灬Duke
2015/07/20
0
0
面试时sql笔试题都在这里了!

建表 1.创建student和score表 2.为student表和score表增加记录 问题:插入的数据出现乱码是什么原因? 答:有可能是默认字符集出错。 可在navicate 下按F6打开命令行界面,然后输入下面的语句...

青春的小奋斗
2018/08/19
0
0
MySQL存储过程中使用动态行转列

本文介绍的实例成功的实现了动态行转列。下面我以一个简单的数据库为例子,说明一下。 数据表结构 这里我用一个比较简单的例子来说明,也是行转列的经典例子,就是学生的成绩 三张表:学生表...

HenrySun
2016/07/12
105
0

没有更多内容

加载失败,请刷新页面

加载更多

计算机实现原理专题--二进制减法器(二)

在计算机实现原理专题--二进制减法器(一)中说明了基本原理,现准备说明如何来实现。 首先第一步255-b运算相当于对b进行按位取反,因此可将8个非门组成如下图的形式: 由于每次做减法时,我...

FAT_mt
今天
5
0
好程序员大数据学习路线分享函数+map映射+元祖

好程序员大数据学习路线分享函数+map映射+元祖,大数据各个平台上的语言实现 hadoop 由java实现,2003年至今,三大块:数据处理,数据存储,数据计算 存储: hbase --> 数据成表 处理: hive --> 数...

好程序员官方
今天
7
0
tabel 中含有复选框的列 数据理解

1、el-ui中实现某一列为复选框 实现多选非常简单: 手动添加一个el-table-column,设type属性为selction即可; 2、@selection-change事件:选项发生勾选状态变化时触发该事件 <el-table @sel...

everthing
今天
6
0
【技术分享】TestFlight测试的流程文档

上架基本需求资料 1、苹果开发者账号(如还没账号先申请-苹果开发者账号申请教程) 2、开发好的APP 通过本篇教程,可以学习到ios证书申请和打包ipa上传到appstoreconnect.apple.com进行TestF...

qtb999
今天
10
0
再见 Spring Boot 1.X,Spring Boot 2.X 走向舞台中心

2019年8月6日,Spring 官方在其博客宣布,Spring Boot 1.x 停止维护,Spring Boot 1.x 生命周期正式结束。 其实早在2018年7月30号,Spring 官方就已经在博客进行过预告,Spring Boot 1.X 将维...

Java技术剑
今天
18
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部