MsSql分区函数的使用

最新推荐文章于 2022-11-03 11:35:56 发布

weixin_30421809

最新推荐文章于 2022-11-03 11:35:56 发布

阅读量114

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/deymmtd/archive/2010/09/17/1829532.html

本文通过具体案例展示了如何使用SQL进行数据排名及子查询操作，包括如何获取每个员工最新的三条订单记录，以及如何找出成绩表中每班的前三名学生。

例１：根据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

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

转载于:https://www.cnblogs.com/deymmtd/archive/2010/09/17/1829532.html