首页 > 代码库 > SQLServer 窗口函数

SQLServer 窗口函数

一、窗口函数的作用

  窗口函数是对一组值进行操作,不需要使用GROUP BY 子句对数据进行分组,还能够在同一行中同时返回基础行的列和聚合列。窗口函数,基础列和聚合列的查询都非常简单。

二、语法格式

  窗口函数的语法格式如下:

OVER([PARTITION BY value_expression,..[n] ] <ORDER BY BY_Clause>)
  • PARTITION:分组;
  • ORDER BY:排序;

  首先建一张调试表如下:

CREATE TABLE [dbo].[xxx](  [Id] [int] NULL,  [Name] [nvarchar](50) NULL,  [Operate] [nvarchar](50) NULL,  [Score] [int] NULL,  [CreateTime] [datetime] NULL) ON [PRIMARY]

  往里面添加如下数据。

  

三、应用场景

  1、聚合列与数据列共同显示

 --查询姓名、分数、以及全体平均分 SELECT Name, Score, CAST(AVG(Score) OVER() AS decimal(5,2) )AS 平均分 FROM xxx

  

  2、分组日期最新

--对每个人查询日期最新列SELECT * FROM (    SELECT row_number() OVER(PARTITION BY Name ORDER BY CreateTime) AS part ,Score, Name, CreateTime FROM xxx) AS CWHERE C.part = 1

 

  3、分页

  返回结果集内的行号,每个分区从1开始,ORDER BY可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。

四、排名函数

  1、ROW_NUMBER()

  返回结果集内的行号,每个分区从1开始计算,ORDER BY可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。

    SELECT row_number() OVER(PARTITION BY Name ORDER BY CreateTime) ,Score, Name, CreateTime    FROM xxx

   输出如下:

  

  2、RANK()

  返回结果集的分区内每行的排序。行的排名是相关行之前的排名数加1。如果两个或多个行与一个排名关联,则每个关联行将得到相同的排名。

  SELECT RANK() OVER(PARTITION BY Name ORDER BY SCORE) ,Score, Name, CreateTime  FROM xxx

  下面一张图片很好地说明了Rank与ROW_NUMBER的区别。

  

  3、DENSE_RANK()

  
  4、NTILE()

   

SQLServer 窗口函数