SQL server 2005中新增的排序函数及应用

先来个例子

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

注意，具有相同数量的两个订单是怎样都被评价为#4的。RANK和DENSE_RANK之间的不同之处在于，在平级的结果后他们是如何重新开始计数的。RANK绕过尽可能多的平级的结果。在我们上面的示例中，因为有两个结果绑定在#4上，因此跟随其后的#5被跳过而评价等级以#6继续。另一方面，DENSE_RANK从下一个整数继续。如果我们在上面的查询中使用函数名DENSE_RANK代替RANK，那么Tito在2005年12月18日相应于单价$12.44的订单评价将是#5。