sql server2005新特性
先來個例子
SELECT
YEAR(BirthDate),
ROW_NUMBER() OVER(ORDER BY YEAR(BirthDate)) AS 'RowNumber',/* 按年產生一個唯一的序號 */
ROW_NUMBER() OVER(PARTITION BY YEAR(BirthDate) ORDER BY BirthDate) AS RowNumberPartition, /* 每年產生一個唯一的序列 */
RANK() OVER(ORDER BY YEAR(BirthDate)) AS 'Rank', /* 產生一個非緊密排名 */
DENSE_RANK() OVER(ORDER BY YEAR(BirthDate)) AS 'Dense_Rank', /* 產生一個緊密排名 */
NTILE(10) OVER(ORDER BY BirthDate DESC) AS 'ntile' /* 將結果分成10個組 */
FROM HumanResources.Employee
ORDER BY BirthDate
看看新的排序函數如何解決SQL server 2000中不方便解決的問題
--按BirthDate排序，取第10條到20條的數據 (這一定是最激動人心的新特性，哈哈)
SELECT BirthDate FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY BirthDate) AS a,BirthDate
FROM HumanResources.Employee
) AS a
WHERE a BETWEEN 10 AND 20
--將數據分成十份，取第三份
SELECT
BirthDate
FROM
(
SELECT
NTILE(10) OVER(ORDER BY BirthDate DESC) AS a,BirthDate
FROM HumanResources.Employee
) AS a
WHERE a = 3
再來看看如何用新的排序函數解決以前在SQL server 2000中的問題
-- 出生的員工最多的一年出生多少員工（有點）
/* 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%的數據
/* 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