SQL server 2005中新增的排序函數(shù)及應(yīng)用

duduwolf 2005-11-12

展開(kāi)全文

先來(lái)個(gè)例子

SELECT
    YEAR(BirthDate),
    ROW_NUMBER() OVER(ORDER BY YEAR(BirthDate)) AS ‘RowNumber‘,/* 按年產(chǎn)生一個(gè)唯一的序號(hào) */
    ROW_NUMBER() OVER(PARTITION BY YEAR(BirthDate) ORDER BY BirthDate) AS RowNumberPartition, /* 每年產(chǎn)生一個(gè)唯一的序列 */
    RANK() OVER(ORDER BY YEAR(BirthDate)) AS ‘Rank‘, /* 產(chǎn)生一個(gè)非緊密排名 */
    DENSE_RANK() OVER(ORDER BY YEAR(BirthDate)) AS ‘Dense_Rank‘, /* 產(chǎn)生一個(gè)緊密排名 */
    NTILE(10) OVER(ORDER BY BirthDate DESC) AS ‘ntile‘ /* 將結(jié)果分成10個(gè)組 */
FROM HumanResources.Employee
ORDER BY BirthDate

看看新的排序函數(shù)如何解決SQL server 2000中不方便解決的問(wèn)題

--按BirthDate排序，取第10條到20條的數(shù)據(jù) (這一定是最激動(dòng)人心的新特性，哈哈)
SELECT BirthDate FROM
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY BirthDate) AS a,BirthDate
    FROM HumanResources.Employee
) AS a
WHERE a BETWEEN 10 AND 20

--將數(shù)據(jù)分成十份，取第三份
SELECT
    BirthDate
FROM
(
SELECT
    NTILE(10) OVER(ORDER BY BirthDate DESC) AS a,BirthDate
FROM HumanResources.Employee
) AS a
WHERE a = 3

再來(lái)看看如何用新的排序函數(shù)解決以前在SQL server 2000中的問(wèn)題

-- 出生的員工最多的一年出生多少員工（有點(diǎn)）
/* SQL server 2000 */
SELECT MAX(a)
FROM
(
SELECT COUNT(EmployeeID) AS a FROM HumanResources.Employee GROUP BY YEAR(BirthDate)
) AS a

/* SQL server 2005  */
SELECT MAX(a)
FROM
(
SELECT
    ROW_NUMBER() OVER(PARTITION BY YEAR(BirthDate) ORDER BY BirthDate) AS a
FROM HumanResources.Employee
) AS a

-- 出生日期涵蓋了多少年
/* SQL server 2000  */
SELECT COUNT(DISTINCT YEAR(BirthDate)) FROM HumanResources.Employee

/* SQL server 2005 */
SELECT MAX(a)
FROM
(
SELECT
    DENSE_RANK() OVER(ORDER BY YEAR(BirthDate)) AS a
FROM HumanResources.Employee
) AS a

-- 顯示前10%的數(shù)據(jù)
/* SQL server 2000  */
SELECT TOP 10 Percent BirthDate FROM HumanResources.Employee

/* SQL server 2005  */
SELECT
    BirthDate
FROM
(
SELECT
    NTILE(10) OVER(ORDER BY BirthDate DESC) AS a,BirthDate
FROM HumanResources.Employee
) AS a
WHERE a = 1

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買(mǎi)等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來(lái)自： duduwolf > 《編程》

舉報(bào)/認(rèn)領(lǐng)