SqlServer窗口函数

窗口函数的作用

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

语法格式

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

over([partition by value_expression,...,[n]],<order by by\_value>)

• partition by：分组
• order by：排序

首先创建一张测试表：

CREATE TABLE [dbo].[scores]( [Id] [int] IDENTITY(1,1) NOT NULL,[StudentId] [int] NULL,[CSharp] [float] NULL,[sqlServer] [float] NULL,[C语言] [float] NULL )

表中数据如下：

应用实例

1、avg()求平均值：

/*用group by分组 */
SELECT StudentId,CSharp,AVG(CSharp) AS '平均分' FROM scores group by StudentId,CSharp

/*用over()分组，group by可省略 *partition by studentid,CSharp 表示按studentid,CSharp分组 */
SELECT StudentId,AVG(csharp) over(partition by studentid,CSharp) AS '平均分' from scores

/*用over()分组，group by可省略 *默认情况下所有数据为同一分组 */
SELECT StudentId,AVG(CSharp) over() AS '平均分' FROM scores 

/*用over()分组，group by可省略 *partition by StudentId表示按StudentId分组 */
SELECT StudentId,AVG(CSharp) over(partition by StudentId) AS '平均分' FROM scores 

2、cast()转换数据格式
语法格式：cast(原数据 as 新格式)

/*用group by分组 *将avg(CSharp)转换为decimal(5,2)格式 */
select StudentId,cast(AVG(csharp) as decimal(5,2)) as'平均分' from scores group by StudentId,CSharp

/*用over()分组，group by可省略 *partition by studentid,CSharp表示按studentid,CSharp分组 *将avg(CSharp)转换为decimal(5,cast(AVG(csharp) over(partition by studentid,CSharp) as decimal(5,2)) as'平均分' from scores

/*用over()分组，group by可省略 *默认情况下所有数据为同一分组 *将avg(CSharp)转换为decimal(5,cast(AVG(csharp) over() as decimal(5,2)) as'平均分' from scores

/*用over()分组，group by可省略 *partition by studentid表示按studentid分组 *将avg(CSharp)转换为decimal(5,cast(AVG(csharp) over(partition by studentid) as decimal(5,2)) as'平均分' from scores

3、row_number()创建列编号

select row_number() over(partition by StudentId order by CSharp asc) as rowNumber,StudentId,CSharp from scores

4、rank()排序
返回结果集的分区内每行数据的顺序，行的排名是从1开始算。如果两个或多个行的数据相同，则每个关联行将得到相同的排名。

select rank() over(partition by StudentId order by CSharp),CSharp from dbo.scores

select rank() over(order by CSharp),CSharp from dbo.scores