SQL Server 2012  开窗函数

-- 开窗函数：在结果集的基础上进一步处理(聚合操作)-- Over函数,添加一个字段显示最大年龄SELECT * ,    MAX(StuAge) OVER ( ) MaxStuAgeFROM  dbo.Student;-- Over函数,添加一个字段显示总人数SELECT * ,    COUNT(StuID) OVER ( ) StuCountFROM  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 ( ) AgeHeightFROM  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;