精华内容
下载资源
问答
  • Mysql排序函数

    千次阅读 2018-04-19 17:16:40
    一、row_numberrow_number会为查询出来的每条记录生成一个序号,依次排序并且不会重复,row_number必须要使用over句子选择对某一列进行排序才会生成序号,row_number用法实例:select ROW_NUMBER() OVER(order by ...

    一、row_number

    row_number会为查询出来的每条记录生成一个序号,依次排序并且不会重复,row_number必须要使用over句子选择对某一列进行排序才会生成序号,row_number用法实例:

    select ROW_NUMBER() OVER(order by [SubTime] desc) as row_num,* from [Order]

    row_num就是row_number函数生成的序号列,其基本原理是先使用over子句中的排序语句对记录进行排序,然后按照这个顺序生成序号。over中的order by和SQL语句中的order by没有任何的关系,这两处的order by可以完全的不同,例如下述的用法:

    select ROW_NUMBER() OVER(order by [SubTime] desc) as row_num,* from [Order] order by [TotalPrice] desc

    row_number函数可以实现web程序的分页,查询制定范围内的数据,例如根据订单提交时间倒序排列获取第三至第五数据:

    with orderSection as
    (
        select ROW_NUMBER() OVER(order by [SubTime] desc) rownum,* from [Order]
    )
    select * from [orderSection] where rownum between 3 and 5 order by [SubTime] desc

    在使用row_number函数实现分页的时候要特别注意一点就是over后面的order by与sql中的order by 要保持一直,否则得到的序列可能不是连续序列,例如:

    with orderSection as
    (
        select ROW_NUMBER() OVER(order by [SubTime] desc) rownum,* from [Order]
    )
    select * from [orderSection] where rownum between 3 and 5 order by [TotalPrice] desc

    二、rank

    rank函数与row_number函数不同的一点就是考虑到了over子句中排序字段值相同的情况,over子句中排序字段值相同的序号是一样的,后面字段值不相同的序号将跳过相同的排名号排下一个,所以其生成序号可能是不连续的,用法如下:

    select RANK() OVER(order by [UserId]) as rank,* from [Order] 

    三、dense_rank

    dense_rank与rank函数不同的是,不会跳过相同排名号的下一个,而是会产生连续的序号,其用法如下:

    select DENSE_RANK() OVER(order by [UserId]) as den_rank,* from [Order]

    四、ntile

    ntile可以对序号进行分组处理,将有序分区中的行分发到指定数目的组中,其有一个参数用来指定桶数。其分组的依据是:

    1.每组的记录数不能大于其上一个组的记录数,即编号小的组记录数不能下雨编号大的组记录数。

    2.所有组的记录是要么都相同,要么从某一个记录较小的数开始后面所有的组的记录数都与这个组的记录数相同。首先查看时候可以平均分配,若不能平均分配的话,则第一组分配(总记录数)/(桶数)+1,然后再看是否可以平均分配。

    具体的用法如下:

    select NTILE(4) OVER(order by [SubTime] desc) as ntile,* from [Order]

    五、row_number&partition_by

    partition_by函数可以将结果进行分组,然后进行排序,可以取出各个组排名范围内的数据,例取出每个组排名前两条的数据:

    select * from (  
    SELECT  ROW_NUMBER()     
            over     
            (PARTITION By name order by val) as rowId,tb_test.*  
    FROM tb_test   
    ) t  
    where rowid <= 2 













    展开全文
  • 碰到这样的需求,我们应该怎么做呢,其实有一个MySQL函数可以非常方便的处理此需求 那就是field()函数 使用方式如下 其中,order by (str,str1,str2,str3,str4……) ,str与str1,str2,str3,str4比较,...
  • 在sql server 使用惯了,习惯了使用row_number() 函数进行排序,但是mysql 确没有这样一个函数。然后找到了po主写的一篇 文章。通过变量赋值来查询的。(PS 我测试的版本是mysql 5.6) 先建表 CREATE TABLE `test` ...
  • MySQL排名函数实现

    千次阅读 2020-07-24 12:00:00
    作者:神易风原文链接(底部链接可直达):https://shenyifengtk.github.io/2020/07/22/MySQL%E6%8E%92%E5%90%8D%E5%87%B...

    作者:神易风

    原文链接(底部链接可直达):https://shenyifengtk.github.io/2020/07/22/MySQL%E6%8E%92%E5%90%8D%E5%87%BD%E6%95%B0%E5%AE%9E%E7%8E%B0/

    现在有个需求对所有学生分数进行排名,并且列出名次。刚看到这个需求,我有点懵逼,完全没有思路????,为什么难一点需求,我就不会做呢???? 去网上查询资料,把所有实现都列出来,全部都要学会。

    数据库准备

    创建一个分数表s_score

    CREATE TABLE `s_score`  (
      `id` int NOT NULL AUTO_INCREMENT,
      `score` int NOT NULL DEFAULT 0,
      `name` varchar(20) CHARACTER SET utf8mb4 NULL,
      PRIMARY KEY (`id`)
    );
    

    插入数据

    INSERT INTO `s_score` (`name`, `score`) VALUES
    ('张三', 80),
    ('小明', 90),
    ('小红', 60),
    ('李四', 70),
    ('赵武', 80),
    ('梁晨', 87),
    ('小绿', 69),
    ('威廉', 69),
    ('大卫', 91),
    ('王五', 96),
    ('赵六', 96),
    ('小五', 80),
    ('小龙', 88);
    

    普通实现

    在MySQL8.0推出Rank排名函数RANK,完全支持这种需求,但是必须MySQL8.0 以上版本才支持这个特性。8.0以下的版本有什么方法实现呢,使用用户变量,记录名次。
    用户变量:以"@"开始,形式为"@var_name",以区分用户变量及列名。它可以是任何随机的,复合的标量表达式,只要其中没有列指定。下面写一个小例子,展示如何使用用户变量

    select @a:=1 a,@b:=@a+1 b
    

    执行结果

    := 是赋值的意思,与编程语言赋值有点区别。下面开始展示使用简单SQL实现RANK排名函数效果

    用户变量简单实现名次显示

    SELECT name,score, @rank:=@rank+1 `rank` from s_score s,(select @rank:=0) q ORDER BY score desc
    
    namescorerank
    赵六961
    王五962
    大卫913
    小明904
    小龙885
    梁晨876
    小五807
    张三808
    赵武809
    李四7010
    威廉6911
    小绿6912
    小红6013

    并排名次展示

    现在还有一个问题,出现分数相同,并列排名,名次应该相同。我们使用一个temp变量来记录前一个分数值,判断前面分数是否与当前相等,相等直接返回上一个排名情况,否则排名+1。

    select name,score,case when @temp_score=score then @rank when @temp_score:=score then @rank:=@rank+1 END 
       `rank` from s_score s,(select @rank:=0,@temp_score:=NULL) q ORDER BY score desc
    
    namescorerank
    赵六961
    王五961
    大卫912
    小明903
    小龙884
    梁晨875
    小五806
    张三806
    赵武806
    李四707
    威廉698
    小绿698
    小红609

    并排名次跳过

    如果出现并列排名,下一个名次将自动跳过,比如出现两个并列第一,91应该变成第三名了,名次和人数相对应。

    SELECT name,score,rank from (
    SELECT name ,score,@rank :=IF( @temp_score = score, @rank, @rank_incr ) `rank`,@rank_incr := @rank_incr + 1,
      @temp_score := score FROM score s,(SELECT@rank := 0,@temp_rank := NULL,@rank_incr := 1 ) q ORDER BY score DESC) a
    
    namescorerank
    赵六961
    王五961
    大卫913
    小明904
    小龙885
    梁晨876
    小五807
    张三807
    赵武807
    李四7010
    威廉6911
    小绿6911
    小红6013

    使用SQL窗口函数

    窗口函数的基本语法如下:

    select 排序函数/聚合函数 over (<partition by ...>  分区字段   order by  排序字段)

    注意over 后面有一个空格的,这个语法有点蛋疼,我自己试了十几次才书写成功。
    根据维基百科解释:窗口函数允许在当前记录之前和之后访问记录中的数据。窗口函数定义一或一列窗口,其中当前行周围具有给定的长度,并跨窗口中的数据集执行计算。可以这样理解,窗口就是数据集合,函数就是计算数据方法。

    partiton by是可选的。如果不使用partition by,那么就是将整张表作为一个集合,最后使用排序函数得到的就是每一条记录根据排序列的排序编号。
    排序函数主要有rank()、dense_rank、row_number,他们主要区别:

    • rank():   对同一个字段排序,出现相同时,会并列排名,并且会出现排名间隙。

    • dense_rank() :  对同一个字段排序,出现相同时,会出现并列排名,排名连续的

    • row_number():  对同一个字段排序,排名是联系的,即使出现相同,不会并列排名次

       
    select name,score, RANK() over (ORDER BY score DESC) `rank`,ROW_NUMBER() over (order by score DESC) `row`,
    DENSE_RANK()over (ORDER BY score DESC) `dense` from s_score
    
        
    
    namescorerankrowdense
    赵六96111
    王五96121
    大卫91332
    小明90443
    小龙88554
    梁晨87665
    赵武80776
    小五80786
    张三80796
    李四7010107
    小绿6911118
    威廉6911128
    小红6013139

    以上就是排序名次全部实现方式了,还有其他实现方式,麻烦在评论里补充一下。

    参考文档

    https://cloud.tencent.com/developer/article/1562954
    https://www.jianshu.com/p/bb1b72a1623e

    推荐阅读:

    喜欢我可以给我设为星标哦

    好文章,我“在看”

    展开全文
  • mysql排序-四大函数(row_number、rank、dense_rank、ntile)

    千次阅读 热门讨论 2020-07-26 11:46:21
    row_number、rank、dense_rank、ntile——mysql排序 1.row_number 依次排序 row_number在排名时序号 连续 不重复,即使遇到表中的两个2时亦如此。 SELECT e.stuno AS 学号,(SELECT s.stuname FROM stuinfo s ...

    row_number、rank、dense_rank、ntile——mysql排序

    1.row_number 依次排序

    row_number在排名时序号 连续 不重复,即使遇到表中的两个2时亦如此。

    SELECT e.stuno AS 学号,(SELECT s.stuname FROM stuinfo s WHERE s.stuno IN (e.stuno)) AS 姓名,(e.wscores+e.cscores) AS 总分,row_number() OVER(ORDER BY (e.wscores+e.cscores) DESC) AS 排名 FROM exams e;

    注意:在使用row_number实现分页时需要特别注意,over子句中的order by要与Sql排序记录中的order by 保持一致,否则得到的序号可能不是连续的。

    2.rank 叠加、跳动排序

    rank函数会把要求排序的值相同的归为一组且每组序号一样,排序不会连续。

    SELECT e.stuno AS 学号,(SELECT s.stuname FROM stuinfo s WHERE s.stuno IN (e.stuno)) AS 姓名,(e.wscores+e.cscores) AS 总分,rank() OVER(ORDER BY (e.wscores+e.cscores) DESC) AS 排名 FROM exams e;

    3.dense_rank 叠加、依次排序

    dense_rank排序是连续的,也会把相同的值分为一组且每组排序号一样。

    SELECT e.stuno AS 学号,(SELECT s.stuname FROM stuinfo s WHERE s.stuno IN (e.stuno)) AS 姓名,(e.wscores+e.cscores) AS 总分,dense_rank() OVER(ORDER BY (e.wscores+e.cscores) DESC) AS 排名 FROM exams e;

    4.ntile 按序分组

    NTILE(group_num)将所有记录分成group_num个组,每组序号一样。

    SELECT e.stuno AS 学号,(SELECT s.stuname FROM stuinfo s WHERE s.stuno IN (e.stuno)) AS 姓名,(e.wscores+e.cscores) AS 总分,NTILE(2) OVER(ORDER BY (e.wscores+e.cscores) DESC) AS 排名 FROM exams e;

    如果需要分成3组,只要将“NTILE(2)”中2改成3.

    展开全文
  • MySQL分组排序功能

    2012-08-16 13:15:19
    由于MYSQL没有提供类似ORACLE中OVER()这样丰富的分析函数. 所以在MYSQL里需要实现这样的功能,我们只能用一些灵活的办法
  • 窗口函数中,排序函数分三种: rank() over(partition by 分区字段 order by 排序字段 desc/asc) dense_rank()over(partition by 分区字段 order by 排序字段 desc/asc) row_number()over(partition by 分区字段 ...


    重要:Mysql8.0+版本支持窗口函数。

    基础语法

    窗口函数中,排序函数分三种:

    rank() over(partition by 分区字段 order by 排序字段 desc/asc)
    
    dense_rank()over(partition by 分区字段 order by 排序字段 desc/asc)
    
    row_number()over(partition by 分区字段 order by 排序字段 desc/asc)
    
    • rank()函数,当指定字段数值相同,则会产生相同序号记录,且产生序号间隙。
    • dense_rank()函数,当指定字段数值相同,则会产生相同序号记录,且不会产生序号间隙
    • row_number()函数,不区分是否记录相同,产生自然序列

    窗口函数理解

    • over() 用来指定函数执行窗口范围,如果后面括号内无任何内容,则指窗口范围是满足 where 条件所有行。
    • partition by,指定按照某字段进行分组,窗口函数是在不同的分组分别执行。不会减少原表中的行数
    • order by,指定按照某字段进行排序

    关于窗口函数是在不同的分组分别执行,不会减少源表中的行数的理解:
    Scores表:

    idclass_idscore
    001195
    002287
    003192
    004387
    005186
    006293
    007391

    窗口函数查询:

    select class_id,count(id)over(partition by class_id order by class_id) as count from Scores;
    

    结果:

    class_idcount
    13
    13
    13
    22
    22
    32
    32

    group by分组函数查询

    select class_id,count(id) as count_id from Scores group by class_id order by class_id;
    

    结果:

    class_idcount_id
    13
    22
    32

    实例

    leetcode题目:求Scores表的分数排序,如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。
    查询:

    select Score,dense_rank()over(order by Score desc) as "Rank" from Scores
    

    结果:

    scoreRank
    951
    932
    923
    914
    875
    875
    867

    在这里插入图片描述

    展开全文
  • mysql 窗口函数排序

    2020-10-06 17:18:25
    窗口函数rank, dense_rank, row_number select *, rank() over (order by 成绩 desc) as ranking, dense_rank() over (order by 成绩 desc) as dese_rank, row_number() over (order by 成绩 desc) as row_num ...
  • 主要介绍了MySQL查询排序与查询聚合函数用法,结合实例形式分析了MySQL查询结果排序以及查询聚合函数相关使用技巧,需要的朋友可以参考下
  • 窗口的概念非常重要,它可以理解为记录集合,窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。这篇文章主要介绍了Mysql8.0使用窗口函数解决排序问题,需要的朋友可以参考下
  • MySQL自定义排序函数FIELD()

    万次阅读 2018-07-01 12:05:17
    MySQL可以通过field()函数自定义排序 格式: field(value,str1,str2,str3,str4) value与str1、str2、str3、str4比较,返回1、2、3、4,如遇到null或者不在列表中的数据则返回0. 这个函数好像Oracle中没有专...
  • MySQL中不存在类似于SQL Server或Orcal中的rank()函数来得到排名。所以我们需要手动来时先这个函数,先看一下应用场景: 查询每门课成绩最好的前两名: select cid,score,rank from (select tmp.cid,tmp.score,if(@...
  • mysql 按中文字段排序

    2020-12-15 05:30:50
    如果这个问题不解决,那么MySQL将无法实际处理中文。... 如果你使用源码编译MySQL,可以编译MySQL时使用 –with–charset=gbk 参数,这样MySQL就会直接支持中文查找和排序了(默认的是latin1)。也可以用 extra-char
  • MySQL中聚集函数排序、分组大概了解。
  • mysql实现排名函数三种方式

    千次阅读 2019-06-17 21:32:33
    MySQL没有提供排名函数,但是我们可以通过一些技巧来实现开窗函数的效果。 1. 环境搭建、目标结果集 CREATE TABLE `tem` (  `id` int(11) NOT ...
  • rank函数会把要求排序的值相同的归为一组且每组序号一样,排序不会连续 执行 select rank() OVER(order by e.salary desc) as row_num , e.salary from employee e 结果: 3.dense_rank dense_rank排序是连续...
  • MYSQL实现排名函数RANK,DENSE_RANK和ROW_NUMBER

    万次阅读 多人点赞 2019-07-04 09:28:42
    文章目录1. 排名分类1.1 区别RANK,DENSE_RANK和ROW_NUMBER1.2 分组排名2. 准备数据3. 不分组排名3.1 连续排名3.2 ...在MYSQL的最新版本MYSQL8已经支持了排名函数RANK,DENSE_RANK和ROW_NUMBER。但是在就得版本中还...
  • 函数MySQL排序,并添加序列

    千次阅读 2019-07-24 11:55:50
    MySQL排序,并添加序列说明两种实现 说明   SQL Server 数据库函数 row_number() over (order by ……) 函数,使用MySQL替换。   MySQL排序,并添加序列   两种实现   ############### row_...
  • 按表达式或函数分组: #案例:按员工的姓名的长度分组,查询每一组的员工个数,筛选员工个数大于五 SELECT COUNT(*),LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5; 按多...
  • mysql排序同值排名一致

    千次阅读 2017-11-07 18:10:07
    mysql排序同值排名一致
  • Mysql排序方式中的升序与降序

    万次阅读 2018-10-10 17:42:14
    order by 根据排序的属性 desc 为从高到低 order by 根据排序的属性 asc 为从低到高
  • 但随着MySQL8.0中新增了窗口函数之后,针对这类统计就再也不是事了,本文就以常用的排序实例介绍MySQL的窗口函数。 1、准备工作 创建表及测试数据 mysql> use testdb; Database changed /* 创建表 */ mysql> create...
  • MySQL分析函数实现

    万次阅读 2018-10-29 10:09:08
    | MySQL分析函数实现 还好MySQL8.0已经实现了与Oracle相同的分析函数。 1. 实现rownum SET @rn:=0; SELECT @rn:=@rn+1 AS rownum ,e.* FROM emp e; 或者写成: SELECT @rn:=@rn + 1 AS rownum ,e.* FROM ...
  • MySQL对中文进行排序详解 MySQL默认只支持对日期、时间和英文字符串进行排序,如果对中文进行order by很可能得不到想要的结果,如下面的查询并不会按我们所想的根据汉字的拼音进行排序: SELECT * from user order ...
  • 数据库版本:MySQL 5.6.42 条件: 某字段代表该数据的状态取值为非负整数,0表示无状态。 需求: 以该字段升序排序,同时需要将值为0的数据放在最后。 首先我们看一下,表的结构: 正常的使用升序查询结果是这样的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 137,907
精华内容 55,162
关键字:

mysql排序函数

mysql 订阅