MsSql分区函数的使用
例1:根据Northwind数据库,要求查询出每个员工最近的三条订单数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 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<=3
9
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<=3
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
例2:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
(
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