sqlserver 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サーバ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