本章预先想写一些Top和Apply基本的用法,但好像没什么意义,所以删掉了一些无用的东西,只留下几个示例,以保证系列的完整性。
Top和Apply解决的常见问题,如返回每个雇员的3个最新订单,订单的时间越新优先级就越高,但还需要引入一个决胜属性,以确定时间桢的订单的优先级,如可用id作为决胜属性。这里提供的解决方案比其它方案要简单得多,且执行速度更快。
返回每个雇员的3个最新订单:
SELECT empid , orderid , custid , orderdate , requireddate FROM sales.orders AS o1 WHERE orderid IN ( SELECT TOP 3 orderid FROM sales.orders AS o2 WHERE o2.empid = o1.empid ORDER BY orderdate DESC , orderid DESC ) |
运用APPLY解决:
SELECT e.empid , a.orderid , a.custid , a.orderdate , a.requireddate FROM hr.employees AS e CROSS APPLY ( SELECT TOP 3 orderid , custid , orderdate , requireddate FROM sales.orders AS o WHERE o.empid = e.empid ORDER BY orderdate DESC , orderid DESC ) AS a |
先扫描employees 获得empid,对每个empid值对orders表查询返回 该雇员的3个最新订单。这里可以返回多个属性。
还有一种解决方案在特定情况下竟然比使用APPLY运算符的方法还要快,使用ROW_NUMBER函数。先为每个订单计算行号,按empid进行分区,并按orderdate desc, orderid desc 顺序排序。然后在外部查询中,只筛选行号小于或等于3的行。
如下:
SELECT orderid ,
custid ,
orderdate ,
requireddate
FROM ( SELECT orderid ,
custid ,
orderdate ,
requireddate ,
ROW_NUMBER() OVER ( PARTITION BY empid ORDER BY orderdate DESC , orderid DESC ) AS rownum
FROM sales.orders
) AS d
WHERE rownum 3