SQL-排名函数

/*
    人员：LDH
    功能：SQL-排名函数
    日期：2018-7-18
 */

USE TSQLFundamentals2008;
GO

-- ROW_NUMBER()
SELECT ROW_NUMBER() OVER (ORDER BY lastname ASC) AS 'RowNo',
    lastname,
    firstname
FROM HR.Employees;

-- RANK
SELECT RANK() OVER (ORDER BY country ASC) AS 'RANK排名',
    country,
    lastname,
    firstname
FROM HR.Employees;

-- DENSE_RANK
SELECT DENSE_RANK() OVER (ORDER BY country ASC) AS '密集排名DENSE_RANK',
    DENSE_RANK() OVER (ORDER BY lastname ASC) AS '密集排名名字',
    country,
    lastname,
    firstname
FROM HR.Employees;

SELECT custid,
    orderdate,
    orderid,
    RowNo = ROW_NUMBER() OVER (PARTITION BY custid
                               ORDER BY orderdate,
                                   orderid
                              )
FROM Sales.Orders;