SQL Server 2012 开窗函数

废话不多说了，直接给大家贴代码了，具体代码如下所示：

-- 开窗函数：在结果集的基础上进一步处理(聚合操作)
-- 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;

以上所述是小编给大家介绍的sql server 2012  开窗函数，希望对大家有所帮助