精华内容
下载资源
问答
  • 排名函数Sql Server2005新增的功能,下面简单介绍一下他们各自的用法和区别。我们新建一张Order表并添加一些初始数据方便我们查看效果。   CREATE TABLE [dbo].[Order]( [ID] [int] IDENTITY(1,1) NOT ...

    原文出至:https://www.cnblogs.com/52XF/p/4209211.html

    排名函数是Sql Server2005新增的功能,下面简单介绍一下他们各自的用法和区别。我们新建一张Order表并添加一些初始数据方便我们查看效果。

     

    复制代码

    CREATE TABLE [dbo].[Order](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [UserId] [int] NOT NULL,
        [TotalPrice] [int] NOT NULL,
        [SubTime] [datetime] NOT NULL,
     CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET IDENTITY_INSERT [dbo].[Order] ON 
    
    GO
    INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (1, 1, 100, CAST(0x0000A419011D32AF AS DateTime))
    GO
    INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (2, 2, 500, CAST(0x0000A419011D40BA AS DateTime))
    GO
    INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (3, 3, 300, CAST(0x0000A419011D4641 AS DateTime))
    GO
    INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (4, 2, 1000, CAST(0x0000A419011D4B72 AS DateTime))
    GO
    INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (5, 1, 520, CAST(0x0000A419011D50F3 AS DateTime))
    GO
    INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (6, 2, 2000, CAST(0x0000A419011E50C9 AS DateTime))
    GO
    SET IDENTITY_INSERT [dbo].[Order] OFF
    GO
    ALTER TABLE [dbo].[Order] ADD  CONSTRAINT [DF_Order_SubTime]  DEFAULT (getdate()) FOR [SubTime]
    GO

    复制代码

     

     

      附上表结构和初始数据图:

      表结构和初始数据-晓菜鸟

     

    一、ROW_NUMBER

      row_number的用途的非常广泛,排序最好用他,一般可以用来实现web程序的分页,他会为查询出来的每一行记录生成一个序号,依次排序且不会重复,注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。row_number用法实例:

     

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

     

      查询结果如下图所示:

      row_number查询结果-晓菜鸟

      图中的row_num列就是row_number函数生成的序号列,其基本原理是先使用over子句中的排序语句对记录进行排序,然后按照这个顺序生成序号。over子句中的order by子句与SQL语句中的order by子句没有任何关系,这两处的order by 可以完全不同,如以下sql,over子句中根据SubTime降序排列,Sql语句中则按TotalPrice降序排列。

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

      查询结果如下图所示:

      over子句和sql语句中的order by 可完全不同-晓菜鸟

      利用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实现分页-晓菜鸟

      注意:在使用row_number实现分页时需要特别注意一点,over子句中的order by 要与Sql排序记录中的order by 保持一致,否则得到的序号可能不是连续的。下面我们写一个例子来证实这一点,将上面Sql语句中的排序字段由SubTime改为TotalPrice。另外提一下,对于带有子查询和CTE的查询,子查询和CTE查询有序并不代表整个查询有序,除非显示指定了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

      查询结果如下图所示:

      over子句中的order by 与sql排序的order by 不一致-晓菜鸟

      

    二、RANK

      rank函数用于返回结果集的分区内每行的排名, 行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名,与row_number函数不同的是,rank函数考虑到了over子句中排序字段值相同的情况,如果使用rank函数来生成序号,over子句中排序字段值相同的序号是一样的,后面字段值不相同的序号将跳过相同的排名号排下一个,也就是相关行之前的排名数加一,可以理解为根据当前的记录数生成序号,后面的记录依此类推。可能我描述的比较苍白,理解起来也比较吃力,我们直接上代码,rank函数的使用方法与row_number函数完全相同。

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

      查询结果如下图所示:

      使用rank函数排名-晓菜鸟

      由上图可以看出,rank函数在进行排名时,同一组的序号是一样的,而后面的则是根据当前的记录数依次类推,图中第一、二条记录的用户Id相同,所以他们的序号是一样的,第三条记录的序号则是3。  

     

    三、DENSE_RANK

      dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时,将不跳过相同排名号,rank值紧接上一次的rank值。在各个分组内,rank()是跳跃排序,有两个第一名时接下来就是第四名,dense_rank()是连续排序,有两个第一名时仍然跟着第二名。将上面的Sql语句改由dense_rank函数来实现。

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

      查询结果如下图所示:

      使用dense_rank函数排名-晓菜鸟

      图中第一、二条记录的用户Id相同,所以他们的序号是一样的,第三条记录的序号紧接上一个的序号,所以为2不为3,后面的依此类推。

    四、NTILE

      ntile函数可以对序号进行分组处理,将有序分区中的行分发到指定数目的组中。 各个组有编号,编号从一开始。 对于每一个行,ntile 将返回此行所属的组的编号。这就相当于将查询出来的记录集放到指定长度的数组中,每一个数组元素存放一定数量的记录。ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引(从1开始)。也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数,用来指定桶数。下面的SQL语句使用ntile函数对Order表进行了装桶处理:

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

      查询结果如下图所示:

      使用ntile排名函数-晓菜鸟

      Order表的总记录数是6条,而上面的Sql语句ntile函数指定的组数是4,那么Sql Server2005是怎么来决定每一组应该分多少条记录呢?这里我们就需要了解ntile函数的分组依据(约定)。

      ntile函数的分组依据(约定):

      1、每组的记录数不能大于它上一组的记录数,即编号小的桶放的记录数不能小于编号大的桶。也就是说,第1组中的记录数只能大于等于第2组及以后各组中的记录数。

      2、所有组中的记录数要么都相同,要么从某一个记录较少的组(命名为X)开始后面所有组的记录数都与该组(X组)的记录数相同。也就是说,如果有个组,前三组的记录数都是9,而第四组的记录数是8,那么第五组和第六组的记录数也必须是8。

      这里对约定2进行详细说明一下,以便于更好的理解。

      首先系统会去检查能不能对所有满足条件的记录进行平均分组,若能则直接平均分配就完成分组了;若不能,则会先分出一个组,这个组分多少条记录呢?就是 (总记录数/总组数)+1 条,之所以分配 (总记录数/总组数)+1 条是因为当不能进行平均分组时,总记录数%总组数肯定是有余的,又因为分组约定1,所以先分出去的组需要+1条。

      分完之后系统会继续去比较余下的记录数和未分配的组数能不能进行平均分配,若能,则平均分配余下的记录;若不能,则再分出去一组,这个组的记录数也是(总记录数/总组数)+1条。

      然后系统继续去比较余下的记录数和未分配的组数能不能进行平均分配,若能,则平均分配余下的记录;若还是不能,则再分配出去一组,继续比较余下的......这样一直进行下去,直至分组完成。

      举个例子,将51条记录分配成5组,51%5==1不能平均分配,则先分出去一组(51/5)+1=11条记录,然后比较余下的 51-11=40 条记录能否平均分配给未分配的4组,能平均分配,则剩下的4组,每组各40/4=10 条记录,分配完成,分配结果为:11,10,10,10,10,晓菜鸟我开始就错误的以为他会分配成 11,11,11,11,7。

      根据上面的两个约定,可以得出如下的算法:

     

    复制代码

    //mod表示取余,div表示取整.
    if(记录总数 mod 桶数==0)
    {
      recordCount=记录总数 div 桶数;
      //将每桶的记录数都设为recordCount.
    }
    else
    {
      recordCount1=记录总数 div 桶数+1;
      int n=1;//n表示桶中记录数为recordCount1的最大桶数.
      m=recordCount1*n;
      while(((记录总数-m) mod (桶数- n)) !=0)
      {
        n++;
        m=recordCount1*n;
      }
      recordCount2=(记录总数-m) div (桶数-n);
      //将前n个桶的记录数设为recordCount1.
      //将n+1个至后面所有桶的记录数设为recordCount2.
    }

    复制代码

     

     NTILE()函数算法实现代码

      

      根据上面的算法,如果总记录数为59,总组数为5,则 n=4 , recordCount1=12 , recordCount2=11,分组结果为 :12,12,12,12,11。

      如果总记录数为53,总组数为5,则 n=3 , recordCount1=11 , recordCount2=10,分组结果为:11,11,11,10,10。

      就拿上面的例子来说,总记录数为6,总组数为4,通过算法得到 n=2 , recordCount1=2 , recordCount2=1,分组结果为:2,2,1,1。

     

    select ntile,COUNT([ID]) recordCount from 
    (
        select NTILE(4) OVER(order by [SubTime] desc) as ntile,* from [Order]
    ) as t
    group by t.ntile

     

      运行Sql,分组结果如图:

      使用ntilt()函数分组-晓菜鸟

      比对算法与Sql Server的分组结果是一致的,说明算法没错。:)

     

    总结:

    在使用排名函数的时候需要注意以下三点:

      1、排名函数必须有 OVER 子句。

      2、排名函数必须有包含 ORDER BY 的 OVER 子句。

      3、分组内从1开始排序。

     

    感谢:

      在博文的最后我要感谢园友 海岸线,他写的 SQL2005四个排名函数(row_number、rank、dense_rank和ntile)的比较 对我帮助很大,非常感谢!

     

    感谢您怀着耐心看完整篇博文!!!
    如果文章有什么错误或不当之处,请您斧正!
    您有任何意见或者建议,您可以给我发邮件,也可以在下面留言,我看到了会第一时间回复您的,谢谢!

    展开全文
  • SQL排序函数

    千次阅读 2019-11-16 16:05:28
    排序值相同时序号不同,依次排序且不会重复,注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。 其基本原理是先使用over子句中的排序语句对记录进行排序,然后按照这个顺序生成序号。 ...

    1. ROW_NUMBER()
    排序值相同时序号不同,依次排序且不会重复,注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

    其基本原理是先使用over子句中的排序语句对记录进行排序,然后按照这个顺序生成序号。
    over子句中的order by子句与SQL语句中的order by子句没有任何关系,这两处的order by 可以完全不同

    2. RANK()
    rank函数用于分区内的排名,排序值相同时序号一样,但为跳跃排序(比如两个第一名后面是第三名)

    3. DENSE_RANK()
    类似RANK(), 排序值相同时序号一样,但为连续排序(比如两个第一名后面是第二名)

    4. NTILE(n)
    n: 代表分多少组,是自动进行分组 (每组的记录数不能大于它上一组的记录数)

    一个图表示几个函数的区别:

     

    总结:
    在使用排名函数的时候需要注意:
      1、排名函数必须有包含 ORDER BY 的 OVER 子句。
      2、分组内从1开始排序。

    常用语法格式:

    1. ROW_NUMBER OVER(partition by … order by … desc):无并列,相同名次顺序排列
    2. RANK OVER(partition by … order by … desc):有并列,跳跃排序,有两个第二名时后边跟着的是第四名
    3. DENSE_RANK OVER(partition by … order by … desc):有并列,连续排序,有两个第二名时仍然跟着第三名
    4. NTITLE(组数) OVER(partition by … order by … desc)

     

    参考:https://www.cnblogs.com/52XF/p/4209211.html

    展开全文
  • SQL中几个常用的排序函数

    千次阅读 2019-03-07 15:36:02
    最近使用窗口函数的频率越来越高,这里打算简单介绍一下几个排序的函数,做一个引子希望以后这方面的问题能够更深入的理解,这里先简单介绍一下几个简单的排序函数及其相关子句,这里先从什么是排序开始吧。...

    最近使用窗口函数的频率越来越高,这里打算简单介绍一下几个排序的函数,做一个引子希望以后这方面的问题能够更深入的理解,这里先简单介绍一下几个简单的排序函数及其相关子句,这里先从什么是排序开始吧。

    排序函数是做什么的?

        排序函数的作用是基于一个结果集返回一个排序值。排序值就是一个数字,这个数字是典型的以1开始且自增长为1的行值。由ranking函数决定排序值可以使唯一的对于当前结果集,或者某些行数据有相同的排序值。在接下来我将研究不同的排序函数以及如何使用这些函数。

    使用RANK函数的例子

        RANK函数每个分区的排序都是从1开始。“partition”是一组有相同指定分区列值的数据行的集合。如果一个分区中有相同排序列的值(这个列指定在ORDER BY后面),然后相同排序列值的行将会分配给相同的排序值。有点绕口,为了更好的理解,如何使用,让我们看下下面的语法:

    RANK ( ) OVER ( [ PARTITION BY <partition_column> ] ORDER BY <order_by_column> )

    这里有几个参数:

    • <partition_column>: 指定一个或者多个列名作为分区数据
    • <order by column>: 确定一个或者多个列然后用来对每个分区的输出数据进行排序

    注意:

    PARTITION BY子句是一个可选项。如是不使用,数据将按照一个分区对所有数据进行排序。如果指定了PARTITION BY子句,则每个分区的数据集都各自进行从1开始的排序。

    现在对RANK函数的语法和如何工作有了一定的理解,下面运行一对该函数的例子。需要说明一下我的例子的运行环境都是AdventureWorks2012 数据库,可以从网络上下载这里给出一个下载地址http://msftdbprodsamples.codeplex.com/releases/view/93587

    下面是第一个使用RANK函数的例子:

    1

    2

    3

    4

    5

    6

    7

    USE AdventureWorks2012;

    GO

    SELECT PostalCode, StateProvinceID,

           RANK() OVER

             (ORDER BY PostalCode ASCAS RankingValue

    FROM Person.Address

    WHERE StateProvinceID IN (23,46);

     

    Code1: 只有RANK函数不分区

     

    运行代码后,结果集如下:

    PostalCode      StateProvinceID RankingValue
    --------------- --------------- --------------------
    03064           46              1
    03064           46              1
    03106           46              3
    03276           46              4
    03865           46              5
    83301           23              6
    83402           23              7
    83501           23              8
    83702           23              9
    83864           23              10
     

    如上所示,按照RANK函数使结果集按照列RankingValue进行了排序。在例子中排序是基于列PostalCode。每一个唯一的PostalCode 得到一个不同的排序值。这里PostalCode 为03054 有两行数据,它们的排序值都是1,因为有两个1,所以排序2就被跳过。其余的排序继续往下依次进行。

        由于RANK函数的分区子句没有使用,那么整个结果集被当做一个单一的分区。如果我打算按照独立的StateProvinceID 进行分区,然后进行排序我可以做按照如下的例子来执行:

    1

    2

    3

    4

    5

    6

    7

    8

    USE AdventureWorks2012;

    GO

    SELECT PostalCode, StateProvinceID,

           RANK() OVER

             (PARTITION BY StateProvinceID

              ORDER BY PostalCode ASCAS RankingValue

    FROM Person.Address

    WHERE StateProvinceID IN (23,46);

     

    Code 2: 使用分区子句

    运行代码后的结果集:

    PostalCode      StateProvinceID RankingValue
    --------------- --------------- --------------------
    83301           23              1
    83402           23              2
    83501           23              3
    83702           23              4
    83864           23              5
    03064           46              1
    03064           46              1
    03106           46              3
    03276           46              4
    03865           46              5

     

     

        在输出结果中分为了两个分区,一个分区是StateProvinceID 是23的,而另一个是包含StateProvinceID 值为46的、注意每个分区都是从1开始进行排序的。

    使用DENSE_RANK函数

     

        当运行RANK函数时,由于有一个相同的PostalCode ,输出结果会跳过一个排序值2,通过使用DENSE_RANK函数我能生成一个不省略改相同排序值的一个排序。该函数语法如下:

    DENSE_RANK ( ) OVER ( [ PARTIION BY <partition_column> ] ORDER BY <order_by_column> )

     

    语法中唯一的不同就是函数名称的改变。让我们运行下面的代码来研究下函数:

    1

    2

    3

    4

    5

    6

    7

    8

    USE AdventureWorks2012;

    GO

    SELECT PostalCode, StateProvinceID,

           DENSE_RANK() OVER

             (PARTITION BY StateProvinceID

              ORDER BY PostalCode ASCAS RankingValue

    FROM Person.Address

    WHERE StateProvinceID IN (23,46);

     

    Code3: 使用 DENSE_RANK

    结果集如下:

    PostalCode      StateProvinceID RankingValue
    --------------- --------------- --------------------
    83301           23              1
    83402           23              2
    83501           23              3
    83702           23              4
    83864           23              5
    03064           46              1
    03064           46              1
    03106           46              2
    03276           46              3
    03865           46              4

     

     

        根据结果集,可以看到PostalCode 03064 有相同的排序值,但是下一个PostalCode 的排序值为2而不是3了。与RANK函数的不同就是当有重复排序值时它能保证了排序序列中没有省略排序。

    使用NTILE 函数

    该函数将数据集合划分为不同的组。得到组的数量是根据指定的一个整数来确定的。下面就是NTILE 函数的语法:

    NTILE (integer_expression) OVER ( [ PARTIION BY <partition_column> ] ORDER BY <order_by_column> )

    Where:

    • <integer_expression>: 确定创建不同组的数量
    • <partition_column>:确定一个或者多个列用来进行分区数据
    • <order by column>: 确定一个或者多个列然后用来对每个分区的输出数据进行排序

     

    为了更好地理解,让我们回顾几个不同的例子。运行下面代码:

    1

    2

    3

    4

    5

    6

    7

    USE AdventureWorks2012;

    GO

    SELECT PostalCode, StateProvinceID,

           NTILE(2) OVER

             (ORDER BY PostalCode ASCAS NTileValue

    FROM Person.Address

    WHERE StateProvinceID IN (23,46);

     

    Code4: 使用NTILE 函数查询

    运行结果如下:

    PostalCode      StateProvinceID NTileValue
    --------------- --------------- --------------------
    03064           46              1
    03064           46              1
    03106           46              1
    03276           46              1
    03865           46              1
    83301           23              2
    83402           23              2
    83501           23              2
    83702           23              2
    83864           23              2

     

     

         通过观察结果集,能很容易发现有两个不同的NTileValue 的列值,1和2。两个不同的NTileValue 值被创建是因为这里我查询语句中指定了“NTILE(2)” 。这个括号内的值就是整数表达式,作用就是指定创建的组的数量。当看到结果集中有10行数据,前五行NTileValue 为1,后五行为2。不出所料整个结果集被平均分成了两组。

         如果不能被平均分配到不同个组的时候,比如参数导致有不能被整除的时候。当发生这种情况是那么将不能被整除的行按序放到每一个组内,知道所有的剩余行都被分配完毕。如下所示:

    1

    2

    3

    4

    5

    6

    7

    8

    USE AdventureWorks2012;

    GO

    DECLARE @Integer_Expression int = 4;

    SELECT PostalCode, StateProvinceID,

           NTILE(@Integer_Expression) OVER

             (ORDER BY PostalCode ASCAS NTileValue

    FROM Person.Address

    WHERE StateProvinceID IN (46,23);

     

    Code 5: NTile 查询不能平均分配结果集

    运行代码如下:

    PostalCode      StateProvinceID NTileValue
    --------------- --------------- --------------------
    03064           46              1
    03064           46              1
    03106           46              1
    03276           46              2
    03865           46              2
    83301           23              2
    83402           23              3
    83501           23              3
    83702           23              4
    83864           23              4

     

       这里直奔主题,10个结果行,参数为4需要分成4组,那么10除以4 余数为2。这意味着前两组会多一行比后两组。如上所示,在这个输出结果中1和2组都有3行,然后NTileValue 为3和4的组只有两行。

       跟RANK函数一样,我们也能使用partition 分区子句来创建分区下的NTILE 函数。当引入PARTITION BY 子句时,每个分区内部都从1开始进行NTILE排序。下面展示一下运行代码:

    1

    2

    3

    4

    5

    6

    7

    8

    9

    USE AdventureWorks2012;

    GO

    DECLARE @Integer_Expression int = 3;

    SELECT PostalCode, StateProvinceID,

           NTILE(@Integer_Expression) OVER

             (PARTITION BY StateProvinceID

              ORDER BY PostalCode ASCAS NTileValue

    FROM Person.Address

    WHERE StateProvinceID IN (46,23);

     

    Code 6: 使用分区子句后,使用NTile 查询不平均分组

    运行代码如下:

    PostalCode      StateProvinceID NTileValue
    --------------- --------------- --------------------
    83301           23              1
    83402           23              1
    83501           23              2
    83702           23              2
    83864           23              3
    03064           46              1
    03064           46              1
    03106           46              2
    03276           46              2
    03865           46              3

     

     

       通过结果集可以看到加入分区子句后对NTILE函数的影响。如果观察输出的NTileValue列值,可以发现排序从StateProvinceID  为46开始重新从1开始。这就是加入“PARTITION BY StateProvinceID”子句的作用,先分区在分组排序。

    使用 ROW_NUMBER 函数

        当打算为输出的行生成一个行号时,行号顺序地自增长,步长为1.为了完成目标我们需要使用ROW_NUMBER 函数。

    下面是使用ROW_NUMBER 的例子:

    ROW_NUMBER () OVER ( [ PARTIION BY <partition_expressions> ] ORDER BY <order_by_column> )

     

    代码如下:

    1

    2

    3

    4

    5

    6

    7

    USE AdventureWorks2012;

    GO

    SELECT PostalCode, StateProvinceID,

           ROW_NUMBER() OVER

             (ORDER BY PostalCode ASCAS RowNumber

    FROM Person.Address

    WHERE StateProvinceID IN (46,23);

     

    Code  7: 使用ROW_NUMBER 函数

    运行结果如下:

    PostalCode      StateProvinceID RowNumber
    --------------- --------------- --------------------
    03064           46              1
    03064           46              2
    03106           46              3
    03276           46              4
    03865           46              5
    83301           23              6
    83402           23              7
    83501           23              8
    83702           23              9
    83864           23              10

     

     

    如果想对输出的PostalCode进行排序,但是你打算先按照StateProvinceID进行分组,再排序。为了实现上述要求,我加入PARTITION BY子句,代码如下:

    1

    2

    3

    4

    5

    6

    7

    8

    USE AdventureWorks2012;

    GO

    SELECT PostalCode, StateProvinceID,

           ROW_NUMBER() OVER

             (PARTITION BY StateProvinceID

              ORDER BY PostalCode ASCAS RowNumber

    FROM Person.Address

    WHERE StateProvinceID IN (46,23);

     

    Code 8: 使用PARTITION BY 子句和ROW_NUMBER 函数查询

    运行结果如下:

    PostalCode      StateProvinceID RowNumber
    --------------- --------------- --------------------
    83301           23              1
    83402           23              2
    83501           23              3
    83702           23              4
    83864           23              5
    03064           46              1
    03064           46              2
    03106           46              3
    03276           46              4 

    正如你看到的结果,通过添加分区子句,行数列RowNumber 每个不同的StateProvinceID 值都会从1重新开始排序。

    总结

     

        本篇讲了多种不同的排序数据的方式,并且有一些方式要求分配一个序列化的数字。我先后展示了如何使用ROW_NUMBER, NTILE, RANK 和 DENSE_RANK函数,如何为每一行数据生成序列化的列值。希望能够让大家在使用时更方便,这里也只是展示了一部分窗口函数的使用。还有很多新的窗口函数希望跟大家一起讨论学习。这里只是做一个简单介绍了。

    转载地址:https://www.cnblogs.com/wenBlog/p/6066366.html

    展开全文
  • 注意:在使用row_number实现分页时需要特别注意一点,over子句中的order by要与Sql排序记录中的order by 保持一致,否则得到的序号可能不是连续的。 结果: 2. rank() rank(): 跳跃排序,在每个分组内,如果有两个第...

    为了方便学习和测试,所有的例子都是在Oracle自带用户Scott下建立的。
    我用了mysql的环境,借助以下语句进行建表和插入数据,以备后续练习使用

    create table EMP  --创建表EMP
    (  
      empno    int(4) not null,  
      ename    VARCHAR(10),  
      job      VARCHAR(9),  
      mgr      int(4),  
      hiredate DATE,  
      sal      decimal(7,2),  
      comm     decimal(7,2),  
      deptno   int(2)  
    )  
    
    alter table EMP  add  primary key (EMPNO); --为表追加empno列为主键 
    
    --插入数据
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
      values (7369, 'SMITH', 'CLERK', 7902, str_to_date('1980-12-17', '%Y-%m-%d'), 800, null, 20);  
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
           values (7499, 'ALLEN', 'SALESMAN', 7698, str_to_date('1981-02-20', '%Y-%m-%d'), 1600, 300, 30);  
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
           values (7521, 'WARD', 'SALESMAN', 7698, str_to_date('1981-02-22', '%Y-%m-%d'), 1250, 500, 30);  
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
           values (7566, 'JONES', 'MANAGER', 7839, str_to_date('1981-04-02', '%Y-%m-%d'), 2975, null, 20);  
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
           values (7654, 'MARTIN', 'SALESMAN', 7698, str_to_date('1981-09-28', '%Y-%m-%d'), 1250, 1400, 30);  
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
           values (7698, 'BLAKE', 'MANAGER', 7839, str_to_date('1981-05-01', '%Y-%m-%d'), 2850, null, 30);  
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
           values (7782, 'CLARK', 'MANAGER', 7839, str_to_date('1981-06-09', '%Y-%m-%d'), 2450, null, 10);  
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
           values (7788, 'SCOTT', 'ANALYST', 7566, str_to_date('1987-04-19', '%Y-%m-%d'), 3000, null, 20);  
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
           values (7839, 'KING', 'PRESIDENT', null, str_to_date('1981-11-17', '%Y-%m-%d'), 5000, null, 10);  
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
           values (7844, 'TURNER', 'SALESMAN', 7698, str_to_date('1981-09-08', '%Y-%m-%d'), 1500, 0, 30);  
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
           values (7876, 'ADAMS', 'CLERK', 7788, str_to_date('1987-05-23', '%Y-%m-%d'), 1100, null, 20);  
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
           values (7900, 'JAMES', 'CLERK', 7698, str_to_date('1981-12-03', '%Y-%m-%d'), 950, null, 30);  
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
           values (7902, 'FORD', 'ANALYST', 7566, str_to_date('1981-12-03', '%Y-%m-%d'), 3000, null, 20);  
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
           values (7934, 'MILLER', 'CLERK', 7782, str_to_date('1982-01-23', '%Y-%m-%d'), 1300, null, 10);  
     
    
    • 1.假如现在有这样一个需求,查询每个部门工资最高的雇员的信息,一般面对"最大","最小"问题的时候我们最容易想到的是用max(),min()等聚合函数来解决

    外链接:

    -- 1.查询每个部门工资最高的雇员的信息
    
        select * from
        (SELECT ename,job,hiredate,sal,deptno from emp)t1
        right join
        (select deptno,max(sal) sal from emp group by deptno)t2
        on t1.deptno = t2.deptno and t1.sal = t2.sal
        order by t1.deptno;
    

    思路:我们先看看查询t2表的语句干了什么,

    select deptno,max(sal) sal from emp group by deptno
    

    以deptno 部门分组,同事利用max()筛选出sal工资列的最高数值,查询的结果即为每个部门的最高工资及对应部门;

    这里写图片描述

    我们的需求是每个部门最高工资员工的信息,此时我们已经完成一半,既然需要相关信息那么我们势必需要将全部员工的相关信息先查询出来,再通过与刚才t2表(部门,最高工资)做连接的操作,即以t2表为基表,以deptno与sal值相等的作为限制条件,对两个表做笛卡尔积取结果集,得到结果如下:

    这里写图片描述

    或者我们以内连接方式查询

    
    select * from (select ename, job, hiredate, sal, deptno  
              from emp)t1,  
                   (select deptno, max(sal) sal from emp group by deptno)t2  
             where t1.deptno = t2.deptno  
               and t1.sal = t2.sal  
                order by t1.deptno;  
    

    结果如下:
    这里写图片描述

    –以上是常用内连接简写,我们用标准内连接写法再写一次,注意写法上的区别

    select * from         
                (select ename, job, hiredate, sal, deptno  
                from emp)t1
              inner join
                (select deptno, max(sal) sal from emp group by deptno)t2  
                on t1.deptno = t2.deptno and t1.sal = t2.sal
    order by t1.deptno;  
    
    
    得到的结果当然也是一样的
    

    这里写图片描述


    上面很啰嗦,因为最近从工作中发现自己在连接这块还是很欠缺的,所以针对这最简单的例子进行一个回顾,有益无害吧,大佬们直接略过就好

    接下来才是这篇博客我想总结的点

    • 在满足第一个需求的同时,我们应该习惯性的思考一下是否还有别的方法。答案是肯定的,
      –就是标题中 rank() over(partition by)dense_rank() over(partition by)row_number() over(partition by) 的排序函数

    SQL分别如下:

    • rank() over(partition by)
    
     select t1.empno, t1.ename, t1.job, t1.hiredate, t1.sal, t1.deptno  
            from (select empno, ename, job, hiredate, sal, deptno,
            rank() over (partition by deptno order by sal desc ) Ranklist
            from  emp)t1
            where Ranklist =1;
    

    先看结果:

    这里写图片描述

    可见,得到的结果相同

    那为什么会得出跟上面的语句一样的结果呢?
    这里我们着重分析一下rank()/dense_rank() over(partition by e.deptno order by e.sal desc)这句的语法。

    • over —— 在什么条件之上。
    • partition by e.deptno —— 按部门编号划分(分区)。
    • order by e.sal desc —— 按工资从高到低排序(使用rank()/dense_rank() 时,必须要带order by否则为非法(都说要排序了,再不指定排序的字段是怕是不合适吧?))
    • rank()/dense_rank()/row_number() —— 排序

    那结合起来,整个语句的意思就是:
    在按部门划分的基础上按工资从高到低对雇员进行排序,“排序”由从小到大(desc降序)的数字表示(最小值一定为1)。

    以下两个排序函数的结果

    • dense_rank() over(partition by)
    select t1.empno, t1.ename, t1.job, t1.hiredate, t1.sal, t1.deptno  
            from (select empno,ename,job,hiredate,sal,deptno,
            dense_rank() over (partition by deptno order by sal desc ) Ranklist
            from emp)t1
            where Ranklist = 1;
    

    结果:
    这里写图片描述

    • row_number() over(partition by)
    select t1.empno, t1.ename, t1.job, t1.hiredate, t1.sal, t1.deptno  
            from (select empno,ename,job,hiredate,sal,deptno,
            row_number() over (partition by deptno order by sal desc ) Ranklist
            from emp)t1
            where Ranklist = 1;
    

    结果如下:
    这里写图片描述

    我们可以看到,rank()、dense_rank()结果相同,而row_number()仅出线了以为sal为3000的员工scott

    这时候我们会想是什么导致了以上的差别?


    那我们此时关注的问题便转化为:

    • row_number()、rank()与dense_rank()与之间的区别?
    1.row_number()
    • row_number():暂且称为去重排序,在每个分组内,为查询出来的每一行记录生成一个序号,依次排序且不会重复;
    • 其基本原理是先使用over子句中的排序语句对记录进行排序,然后按照这个顺序生成序号。over子句中的order by子句与SQL语句中的order by子句没有任何关系,这两处的order by 可以完全不同
    1. 在使用 row_number() over()函数时候,over()里头的分组以及排序的执行晚于 where group by order by 的执行。

    2. partition by 用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组,它和聚合函数不同的地方在于它能够返回一个分组中的多条记录,而聚合函数一般只有一个反映统计值的记录。

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

    结果:
    这里写图片描述

    2. rank()
    • rank(): 跳跃排序,在每个分组内,如果有两个第一位时,接下来就是第三位。

         他会对查询出来的记录进行排名,与row_number函数不同的是,
         rank函数考虑到了over子句中排序字段值相同的情况,如果使用rank函数来生成序号;
      

    通过刚才例子中的子句运行结果可清楚得知:

    rank()
    这里写图片描述

    3. dense_rank()
    • dense_rank(): 连续排序,在每个分组内,如果有两个第一级时,接下来仍然是第二级。

    dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。
    dense_rank函数 出现相同排名时,将不跳过相同排名号 ,rank值 紧接 上一次的rank值。在各个分组内,rank()是跳 跃排序,有两个第一名时接下来就是第四名,dense_rank()是连续排序,有两个第一名时仍然跟着第二名。

    结果如下:

    这里写图片描述

    展开全文
  • SQL窗口函数-排序函数

    2020-04-22 23:37:38
    1.排序函数用途 对某一列内容的数值大小,新建一列按1,2,3...排序。 2.排序函数的完整表达式 row_number() over (partition by 分组字段 order by 需排序字段 desc/asc) partition by 分组字段:进行分组排序,...
  • 最初用排序函数时,只会用row_number,后来在网上一看,才知道由于场景不同,是有不同的函数的。 作为一个总结,为以后的使用提供帮助。 一、ROW_NUMBER  对于排序,使用较为广泛的函数就是row_number。据网上说...
  • 排序函数(sqlserver)

    2009-04-26 22:58:47
    排序函数(sqlserver) 在各种处理中应用排序规则的示例 排序规则在拼音处理中的应用 排序规则在全角与半角处理中的应用.sql .......
  • 一般而言,在数据库里面,用来做排序函数主要有四个: row_number、renk、dense_rank、ntile 此处以sqlserser操作方法作解析,此处我把该表放在tmp库下的goosinfo表里,使用数据可移步:数据库练习数据   1、...
  • Oracle中的排序和排序函数的使用

    万次阅读 多人点赞 2019-03-30 23:30:22
    参考地址:https://www.cnblogs.com/qiuting/p/7880500.html 前排提示,下面的实例用到的hr.employees表是Oracle11g的orcl实例自带的表空间的表,不要说你没有~要是没有,... 普通SQL排序 Oracle本身对SQL有良好...
  • 下面的例子和SQL语句均在SQL Server 2008环境下运行通过,使用SQL Server自带的AdventureWorks数据库。
  • 参数: <partition> 将FROM子句生成的结果集划入应用了ROW_NUMBER函数的分区。 <order> 确定将ROW_NUMBER值分配给分区中的行的顺序。有关详细信息,请参阅ORDER BY子句(Transact-SQL)。 返回类型: bigint 类似于...
  • sqlserver2008 排序函数

    千次阅读 2014-02-14 11:35:23
    排序函数: 1.row_number() over(order by liename):根据order by后面的字段排序,为每一行给定一个唯一的行号 select row_number() over( order by iname desc ) as rowid ,iname from test 2.Rank()...
  • SQL 四种排序开窗函数

    千次阅读 2018-04-14 09:53:05
    一 、简单了解什么是开窗函数 什么是开窗函数,开窗函数有什么...二、开窗函数分类 根据使用的目的,开窗函数可以分为两类:聚合开窗函数排序开窗函数。 下面主要解析四种常用的排序开窗函数: 1、ROW_NUMBER(...
  • SQL server排序函数细说

    千次阅读 2014-01-23 14:29:56
    SQL server排序函数细说 排名函数是SQL Server2005新加的功能。在SQL Server2005中有如下四个排名函数:   1. row_number  2. rank  3. dense_rank  4. ntile 一、row_number  row_...
  • 转载:http://www.maomao365.com/?p=5771摘要:在实际的项目开发中,我们经常使用分组函数,对组内数据进行群组后,然后进行组内排序:如:1:取出一个客户一段时间内,最大订单数的行记录2: 取出一个客户一段时间内,...
  • 在窗口分析函数出现之前,存在很多 SQL 难以解决的问题...本文从聚合/排序/极值/移动/切片五大场景来讲解窗口分析函数 窗口分析函数,可以指定数据窗口进行统计分析,它和普通聚合函数的区别: ① 窗口函数对每个组...
  • SQL 排序,筛选,过滤,聚合函数

    千次阅读 2018-05-19 14:02:35
    SQL中第一条索引是1,python索引是0开始】 order by 排序 ASC 升序 DESC 降序 select 列名 from 表名 where 条件  order by 列名 DESC; 【order by 语句后面出现的列名,可以不在select...
  • SqlServer ROW_NUMBER() 排序函数

    千次阅读 2018-02-08 22:19:06
    1.使用row_number()函数进行编号:如select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer原理:先按psd进行排序排序完后,给每条数据进行编号。2.在订单中按价格的升序进行排序...
  • Mysql排序函数

    千次阅读 2018-04-19 17:16:40
    一、row_numberrow_number会为查询出来的每条记录生成一个序号,依次排序并且不会重复,row_number必须要使用over句子选择对某一列进行排序才会生成序号,row_number用法实例:select ROW_NUMBER() OVER(order by ...
  • arrayobj.sort(sortfunction); 参数:sortFunction 可选项。是用来确定元素顺序的函数的名称。... 以上的方法在一维的排序还是很方便的,但像SQL语句中的ORDER BY 一样的多键值排序由怎么做呢? 多维

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 228,073
精华内容 91,229
关键字:

sql排序函数