SQL Server 2012-open Window functions

--open Window function: Further processing on the basis of the result set (aggregation operation)--over function, add a field to show maximum age select *, Max (stuage) over () Maxstuagefrom dbo. student;--over function, add a field to show total number of select *, COUNT (STUID) over () Stucountfrom dbo. student;--Partition by grouping statistics-based on gender groupings, statistics SELECT count (*) over (Partition by stusex), *from dbo. Student; --Based on class grouping, statistics, sorting SELECT COUNT (*) over (PARTITION by Class ORDER by Height), *from dbo. Student; --over function, add a field to show average height select *, avg (height) over () Ageheightfrom dbo. Student;--row_rumber () SELECT row_number () over (ORDER by Stuid DESC) RowNumber, *from dbo. Student--row_rumber () to achieve paging effect;     With T as (SELECT row_number () Up (ORDER by Stuid DESC) RowNumber, * from Dbo.  Student) SELECT * from T WHERE t.rownumber between 1 and 3;--rank () rank function, rank same, skip SELECT Rank () Over (ORDER by Height), *from dbo. Student;--dense_rank () ranking function, same rank do not skip select Dense_rANK () over (ORDER by Height), *from dbo. student;--NTILE () function, Parameters: Total number of records/division area = Array of each region, put the record ordinal into an array (average group) SELECT NTILE (3) over (ORDER by stusex), *from dbo .  Student;

　　

SQL Server 2012-open Window functions