本章預先想寫一些Top和Apply基本的用法，但好像沒什麼意義，所以刪掉了一些無用的東西，只留下幾個示例，以保證系列的完整性。
Top和Apply解決的常見問題，如返回每個僱員的3個最新訂單，訂單的時間越新優先順序就越高，但還需要引入一個決勝屬性，以確定時間楨的訂單的優先順序，如可用id作為決勝屬性。這裡提供的解決方案比其它方案要簡單得多，且執行速度更快。
返回每個僱員的3個最新訂單：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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解決：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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的行。
如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 |
練習：
從學生表中選取對應班級的前num名學生成績
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
--顯示結果 /* bj xh name cj ---------- ---- ---------- ----------- 一班 A006 A6 100 一班 A005 A5 99 一班 A001 A1 89 一班 A002 A2 89 二班 B001 B7 100 二班 B001 B6 99 二班 B001 B9 97 二班 B001 B8 90 二班 B001 B5 88 */ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
-- 建立測試表 declare @student table( ---學生表 bj varchar(10), -- 班級 xh char(4), -- 學號 name varchar(10), -- 姓名 cj int) -- 成績 declare @tj table( ---統計表 bj varchar(10), -- 班級 num int) -- 人數 ：從學生表中選取對應班級的前num名學生成績 set nocount on -- 新增測試資料 insert @student select '一班' ,'A001','A1',89 insert @student select '一班' ,'A002','A2',89 insert @student select '一班' ,'A003','A3',59 insert @student select '一班' ,'A004','A4',80 insert @student select '一班' ,'A005','A5',99 insert @student select '一班' ,'A006','A6',100 insert @student select '一班' ,'A007','A7',82 insert @student select '二班' ,'B001','B1',19 insert @student select '二班' ,'B001','B2',81 insert @student select '二班' ,'B001','B3',69 insert @student select '二班' ,'B001','B4',86 insert @student select '二班' ,'B001','B5',88 insert @student select '二班' ,'B001','B6',99 insert @student select '二班' ,'B001','B7',100 insert @student select '二班' ,'B001','B8',90 insert @student select '二班' ,'B001','B9',97 insert @tj select '一班',3 insert @tj select '二班',5 |
參考SQL：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- 2005.T-SQL select t.bj,s.xh,s.name,s.cj from @tj t cross apply ( SELECT TOP(t.num) with ties -- 加 with ties，一班將選出4個人（2個人並列第三名） xh,name,cj from @student where t.bj=bj -- 加where 功能類似於 inner join ；不加類似於 cross join order by cj desc )s order by case when t.bj='一班' then 1 else 2 end asc,s.cj desc,s.xh asc ---排序 |