MsSql分区函数的使用

例１：根据Northwind数据库，要求查询出每个员工最近的三条订单数据

 with result
2 as
3 (
4    select row_number()over(partition by o.employeeID order by o.orderdate desc )as row ,e.lastName,e.FirstName,o.OrderID
5    from employees e join orders o on e.employeeID=o.employeeID
6 ) 
7 
8 select * from result where row<=３
9 

select e.firstname,e.lastname,ot.* from employees e 

cross apply (select top (2) * from orders o where o.employeeID=e.employeeID order by o.orderdate desc) as ot

order by e.employeeID

 

例２： 

create table Student  --学生成绩表
(
 id int,  --主键
 Grade int, --班级
 Score int --分数
)
insert Student  select 1,1,88
union all select 2,1,66
union all select 3,1,75
union all select 4,2,30
union all select 5,2,70
union all select 6,2,80
union all select 7,2,60
union all select 8,3,90
union all select 9,3,70
union all select 10,3,80

select * from student


select top 3 with ties * from  student order by score desc//前三名

select *,row_number() over(partition by grade order by score desc)as '名次' from student