MsSql分区函数的使用

时间：2010-09-17 来源：幽境仼孓

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

代码 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<=３
9

例２：

代码 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

相关阅读更多 +