標籤:style blog http io ar color 使用 sp strong
一、視窗函數的作用
視窗函數是對一組值進行操作,不需要使用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 視窗函數