文档章节

sql语句 成绩排名

LYQ1990
 LYQ1990
发布于 2017/08/17 15:10
字数 974
阅读 44
收藏 0

sql语句 成绩排名

 

http://www.cnblogs.com/zengguowang/p/5541431.html

-------------------------------------------

思路:有点类似循环里面的自增一样,设置一个变量并赋予初始值,循环一次自增加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.....)}  

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

 

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

 

上面sql执行的结果如下:

  

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

  

 

© 著作权归作者所有

共有 人打赏支持
LYQ1990
粉丝 7
博文 238
码字总数 206220
作品 0
东城
私信 提问
SQL语句经典练习30题:基于Navicat for SQLite本地实现

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

AiFan
11/09
0
0
一道面试题中涉及到的SQL的高级使用

题目是这样的: 3、现在需要做一个全省的学生成绩资料库,系统功能和信息如下: 需要保存的信息有学生ID,学生姓名,学生成绩,所在学校,所在城市,时间 A、请你按照以上需求设计表结构,包...

cjxcloud
2016/06/05
139
0
SQL SERVER使用WITH TIES获取前几行数据

一、SQL SERVER中使用WITH TIES的用途 1、在排名次时,经常遇到取前10名,但刚好第11名(12、13...)的成绩和第10名的一样,我们必须也把后面成绩相同的也取出来,SQL SERVER中WITH TIES语句...

学习也休闲
2016/04/23
42
0
sql语句中不常用但很有用的小用法(转)

< DOCTYPE html PUBLIC -WCDTD XHTML StrictEN httpwwwworgTRxhtmlDTDxhtml-strictdtd> 1、在编写随机出卷系统时,用到随机抽出题库中的N道题目,方法很多,有人使用随机函数等等,其实,一条...

技术小美
2017/11/12
0
0
零基础如何学习SQL——了解select查询语句

如果看这一篇文章对你来说都有些困难的话,你可以先阅读我之前第一篇完全针对彻底零基础的朋友所写的文章,希望能帮助到你。知乎专栏-浅识SQL学习SQL语句,如果你学会了select查询语句,那么...

朴克
2017/01/01
0
0

没有更多内容

加载失败,请刷新页面

加载更多

手写tomcat+servlet

写程序一定要有思路,思路很重要! 一、我们分两步第一步先实现手写tomcat,第二部写servlet 所用技术: 1、soket通信 IO流 2、http请求与相应 3、解析xml 4、java反射技术 导入所需要的jar...

jason_kiss
28分钟前
1
0
Beetl模板的基础用法 【变量、循环、条件】---《Beetl视频课程》(2)

本期视频做了一个博客的首页列表; 内容简介:springboot 集成 beetlsql;使用for循环,使用if控制语句,使用虚拟属性,定义变量等等 一起学beetl目录:https://my.oschina.net/u/1590490?ta...

Gavin-King
34分钟前
1
0
各种视频监控上墙方案的比较

方案1、一使用 DVR 、NVR 直接显示上墙 不得不说,这种办法是成本最低廉的,但这里有不少限制: 无法实现分散点的集中上墙。譬如连锁经营的酒店,如果我在总部建立一个集中上墙的环境,这个就...

PeakFang-BOK
57分钟前
4
0
netfilter 和 iptables

一. netfilter 1. 什么是entfilter 和 iptables netfilter指整个项目名 在这个项目里面,netfilter特指内核中的netfilter框架, iptables指用户空间的配置工具。 netfilter在协议栈中添加了5...

Fc丶
今天
2
0
搞定了微信小程序富文本渲染解决方案-后端渲染方案Html2Wxml2J

先介绍一下最近遇到的问题: 最近小程序项目中有文章详情页需要渲染富文本,微信小程序官方提供的<rich-text>是个弱鸡,很多标签不支持,用起来也麻烦,性能也不咋地。 吐槽完了,我们决定寻...

山东-小木
今天
32
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部