SQL Server Row相關的函數

在課堂上老師要我們找出公司薪資前5名
很簡單

SELECT TOP(5) * FROM 員工
ORDER BY 薪資 DESC

不過下一題就有趣了：
請找出6~10名的

其實也不難，找出2個表
「前10名的表」 去掉「前5名的表」
就可以了

USE 中文北風
GO
--CTE寫法------------------------------------------------ 
WITH T1 AS
(
 --前10名的表
 SELECT TOP(10) 員工編號,姓名,職稱,稱呼,薪資 
 FROM 員工 ORDER BY 薪資 DESC
),
T2 AS
(
 --前5名的表
 SELECT TOP(5) 員工編號,姓名,職稱,稱呼,薪資 
 FROM 員工 ORDER BY 薪資 DESC
)
SELECT * FROM T1
EXCEPT
SELECT * FROM T2


--SQL2000 Oracle 8 前子查詢寫法---------------------------
SELECT TOP(5) 員工編號,姓名,職稱,稱呼,薪資 
FROM 員工 
WHERE 員工編號 NOT IN
 (SELECT TOP(5) 員工編號 FROM 員工 ORDER BY 薪資 DESC)
ORDER BY 薪資 DESC

答案也對，只是效率頗差
資料庫會很忙，他要排序2次(一次前10，一次前5)
並比較後才能得到答案
如果筆數很多，當然查得更慢

所以老師指導可以配合列數函數，只要排一次
接著就像切像蛋糕一樣
你要那一塊，就把那塊切出去給你

/*
 列數語法：
 ROW_NUMBER() -- 逐列遞增，每列都單一個數
 RANK()       -- 遇到重複，則重複列為同數值，下筆不重複列則該數值+重複數
 DENSE_RANK() -- 若有重複，則重複為同數值，下筆不重複列為該數值+1
 NTILE(4)     -- 分成4組，可自由分配幾組，例如要5% → NTILE(20) 
*/


--CTE寫法加上函數------------------------------------------
WITH T1 AS 
(SELECT 員工編號,姓名,職稱,稱呼,薪資 
    ,ROW_NUMBER() OVER (ORDER BY 薪資 DESC) AS 列數 
FROM 員工)
SELECT * FROM T1
WHERE T1.列數 BETWEEN 6 AND 10
--子查詢寫法------------------------------------------------
SELECT * FROM  
 (SELECT 員工編號,姓名,職稱,稱呼,薪資 
     ,ROW_NUMBER() OVER (ORDER BY 薪資 DESC) AS 列數 
 FROM 員工) AS A
WHERE A.列數 BETWEEN 6 AND 10


--RANK()--------------------------------------------------
SELECT 員工編號,姓名,職稱,稱呼,薪資 
    ,RANK() OVER (ORDER BY 薪資 DESC) AS 列數 
FROM 員工

--DENSE_RANK()--------------------------------------------
SELECT 員工編號,姓名,職稱,稱呼,薪資 
    ,DENSE_RANK() OVER (ORDER BY 薪資 DESC) AS 列數 
FROM 員工

--NTILE(4) -----------------------------------------------
SELECT 員工編號,姓名,職稱,稱呼,薪資 
    ,NTILE(4) OVER (ORDER BY 薪資 DESC) AS 列數 
FROM 員工

接著老師說，有更直接作法
有點像是學MySQL的Limit 5,5
但是一定要放在 ORDER BY 之後：

 --注意：OFFSET只能跟ORDER BY
 --OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY
 --排除前5筆 到下個5筆



-----------------------------------------------------------
--OFFSET 5 ROWS 排除前5筆-----------------------------------
SELECT 員工編號,姓名,職稱,稱呼,薪資 
FROM 員工
ORDER BY 薪資 DESC OFFSET 5 ROWS


--排除前5筆到下個5筆(也就是6~10)------------------------------
SELECT 員工編號,姓名,職稱,稱呼,薪資 
FROM 員工
ORDER BY 薪資 DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY

有時除了總流水訂單號外，還會針對每個客戶
紿予遞增的流水單號，若當初INSERT沒有做，
可以SELECT時用上：
ROW_NUMBER() OVER(PARTITION BY 客戶編號)


還有：
LEAD(薪資,2,0)
LAG(薪資,1)
這2個也滿特別的
一個是找此筆中的下2筆同欄的資料，沒有下2筆則補0
一個是找此筆中的上2筆同欄的資料

USE 中文北風
GO

--PARTITION BY 客戶編號：依客戶編號分組給予流水號-------------------
--不一定要跟ORDER BY--------------------------------------------
SELECT 客戶編號,訂單號碼
 ,ROW_NUMBER() OVER(PARTITION BY 客戶編號 ORDER BY 客戶編號) AS 流水號
FROM 訂貨主檔

--LEAD(薪資,2,0)：該筆資料下2筆的薪資，0是最後2筆沒有資料補0-----------
---------------------------------------------------------------
SELECT 員工編號,姓名,職稱,稱呼,薪資
 ,LEAD(薪資,2,0) OVER(ORDER BY 薪資 DESC) AS 下二位的薪資
FROM 員工


--LAG(薪資,1)：該筆資料上1筆的薪資，沒有補0預設為NULL-----------------
---------------------------------------------------------------
SELECT 員工編號,姓名,職稱,稱呼,薪資
 ,LAG(薪資,1) OVER(ORDER BY 薪資 DESC)-薪資 AS 與前一位的薪資差
FROM 員工