MS SQL - 排序函數

MS SQL - 排序函數 

參考資料:
站名:【topcat姍舞之間的極度凝聚】
    [SQL]為查詢的結果加上序號(ROW_NUMBER,RANK,OVER)
 
SQL Server 中的四種排序函數比较
 
SQL語法 排序函數基本運用
 

簡介 :

SQL SERVER 2005 之後所新增的排序函數:
1. ROW_NUMBER(): 依ORDER BY 欄位排序後,給予"連續序號"(EX:常用 分頁查詢)
2. RANK(): 依據相同的值給予同一編號，而下一組"會跳號"(EX:常用 名次排名)
3. DENSE_RANK(): 依據相同的值給予同一編號，而下一組"不會跳號"
4. NTILE(N): 排序後，再將結果分為N組





1.ROW_NUMBER()

SELECT
    ROW_NUMBER() OVER(ORDER BY SEX) AS ROWID, 
    sex, studcname
FROM tblmember

ROW_NUMBER() ↑

2.RANK()

SELECT
    RANK() OVER(ORDER BY SEX) AS ROWID, 
    sex, studcname
FROM tblmember

RANK() ↑

3.DENSE_RANK()

SELECT
    DENSE_RANK() OVER(ORDER BY SEX) AS ROWID, 
    sex, studcname
FROM tblmember

DENSE_RANK() ↑

4.NTILE(N)

SELECT 
    NTILE(5) OVER(ORDER BY SEX) AS ROWID, 
    sex, studcname
FROM tblmember

NTILE(N) ↑

◎進階運用 - 排序後，在指定範圍

  
SELECT * FROM ( 
    SELECT TMPTBL.*, ROW_NUMBER() OVER (ORDER BY idno ASC) AS rownum 
    FROM ( 
        SELECT
            idno, cellphone, email
        FROM
            member
    ) AS TMPTBL
) AS TMPTBL2 WHERE rownum BETWEEN  1 AND 100

卒仔〞筆記 : 筆記 筆記!!!!