你真的會玩SQL嗎？冷落的Top和Apply

你真的會玩SQL嗎？系列目錄

你真的會玩SQL嗎？之邏輯查詢處理階段

你真的會玩SQL嗎？和平大使 内連接配接、外連接配接

你真的會玩SQL嗎？三範式、資料完整性

你真的會玩SQL嗎？查詢指定節點及其所有父節點的方法

你真的會玩SQL嗎？讓人暈頭轉向的三值邏輯

你真的會玩SQL嗎？EXISTS和IN之間的差別

你真的會玩SQL嗎？無處不在的子查詢

你真的會玩SQL嗎？Case也瘋狂

你真的會玩SQL嗎？表表達式，排名函數

你真的會玩SQL嗎？簡單的 資料修改

你真的會玩SQL嗎？你所不知道的 資料聚合

你真的會玩SQL嗎？透視轉換的藝術

你真的會玩SQL嗎？冷落的Top和Apply

你真的會玩SQL嗎？實用函數方法彙總

你真的會玩SQL嗎？玩爆你的資料報表之存儲過程編寫（上）

你真的會玩SQL嗎？玩爆你的資料報表之存儲過程編寫（下）

本章預先想寫一些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       

 練習：

從學生表中選取對應班級的前num名學生成績

--顯示結果
/*
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
*/      

-- 建立測試表
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：

-- 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    ---排序
       

作者：歡醉

公衆号【一個碼農的日常】 技術群：319931204 1号群： 437802986 2号群： 340250479

出處：http://zhangs1986.cnblogs.com/

碼雲：https://gitee.com/huanzui

本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，否則保留追究法律責任的權利。

Top