SQL Server 2012 开窗函数_MsSql_

 -- 开窗函数：在结果集的基础上进一步处理(聚合操作) -- Over函数,添加一个字段显示最大年龄 SELECT * , MAX(StuAge) OVER ( ) MaxStuAge FROM dbo.Student; -- Over函数,添加一个字段显示总人数 SELECT * , COUNT(StuID) OVER ( ) StuCount FROM dbo.Student; -- Partition By 分组统计数量 -- 根据性别分组后，统计 SELECT COUNT(*) OVER ( PARTITION BY StuSex ) , * FROM dbo.Student; -- 根据班级分组后，统计、排序 SELECT COUNT(*) OVER ( PARTITION BY Class ORDER BY Height) , * FROM dbo.Student; -- Over函数,添加一个字段显示平均身高 SELECT * , AVG(Height) OVER ( ) AgeHeight FROM dbo.Student; --Row_Rumber() SELECT ROW_NUMBER() OVER ( ORDER BY StuID DESC ) RowNumber , * FROM dbo.Student --Row_Rumber() 实现分页效果 ; WITH T AS ( SELECT ROW_NUMBER() OVER ( ORDER BY StuID DESC ) RowNumber , * FROM dbo.Student ) SELECT * FROM T WHERE T.RowNumber BETWEEN 1 AND 3; --Rank() 排名函数,名次相同，跳过 SELECT RANK() OVER ( ORDER BY Height ) , * FROM dbo.Student; --DENSE_Rank() 排名函数，名次相同不跳过 SELECT DENSE_RANK() OVER ( ORDER BY Height ) , * FROM dbo.Student; -- NTILE()函数，参数：记录总数/划分区域 = 每个区域数组，把记录序号放进数组 (平均分组) SELECT NTILE(3) OVER ( ORDER BY StuSex ) , * FROM dbo.Student;