代码如下 | 复制代码 |
-- 开窗函数:在结果集的基础上进一步处理(聚合操作) -- Over函数,添加一个字段显示最大年龄 SELECT* , MAX(StuAge) OVER ( ) MaxStuAge FROM dbo.Student; -- Over函数,添加一个字段显示总人数 SELECT* , COUNT(StuID) OVER ( ) StuCount FROM dbo.Student; -- Partition By 分组统计数量 -- 根据性别分组后,统计 SELECTCOUNT(*) OVER ( PARTITION BYStuSex ) , * FROM dbo.Student; -- 根据班级分组后,统计、排序 SELECTCOUNT(*) OVER ( PARTITION BYClassORDERBYHeight) , * FROM dbo.Student; -- Over函数,添加一个字段显示平均身高 SELECT* , AVG(Height) OVER ( ) AgeHeight FROM dbo.Student; --Row_Rumber() SELECTROW_NUMBER() OVER (ORDERBYStuIDDESC) RowNumber , * FROM dbo.Student --Row_Rumber() 实现分页效果 ; WITH TAS(SELECT ROW_NUMBER() OVER (ORDERBYStuIDDESC) RowNumber , * FROM dbo.Student ) SELECT* FROM T WHERE T.RowNumberBETWEEN1AND3; --Rank() 排名函数,名次相同,跳过 SELECTRANK() OVER (ORDERBYHeight ) , * FROM dbo.Student; --DENSE_Rank() 排名函数,名次相同不跳过 SELECTDENSE_RANK() OVER (ORDERBYHeight ) , * FROM dbo.Student; -- NTILE()函数,参数:记录总数/划分区域 = 每个区域数组,把记录序号放进数组 (平均分组) SELECTNTILE(3) OVER (ORDERBYStuSex ) , * FROM dbo.Student; |