--over里面有两个参数

--partition by 用于分割区域   此参数可选

--order by 用于排序      此参数必有

row_number() over(partition by field,order by field desc) as num

select * from  student

--根据分数进行排序作为一个新的索引   不会破坏主键

select name,gender,fenshu, row_number() over(order by fenshu desc) as num from dbo.PeopleInfo

--先用partition by 根据gender将数据分为两个区域，然后在不同的区域根据分数进行分别排序

select name,gender,fenshu, row_number() over(partition by Gender order by fenshu desc) as num from dbo.PeopleInfo

--with  as  生成临时表

with temp as

(

select name,gender,fenshu, row_number() over(partition by Gender order by fenshu desc) as num from student

)

--检索两个区域新索引num=1的

select * from temp where num = 1

---首先格式（）不能丢    再者from后面一定要是表名   select后面如果检索的是字段那么一定要是字段名    所以（）里面的数据一定要as为表名或者字段名

select * from (select name,gender,fenshu, row_number() over(partition by Gender order by fenshu desc) as num from student ) a where num = 1

---子查询中根据score进行排序后按照顺序生成新的含有num field的新表

---在新的table检索前十条数据作为第一页的数据

select *

  from (select p.*,

               row_number() over(order by score, pageindex) as num

          from pagetable as p)

 where num between 0 and 10