SQLServer 排名函数
来源:互联网 发布:软件测试大纲模板 编辑:程序博客网 时间:2024/04/28 04:59
USE MyDB;
IF EXISTS (Select * From sys.objects Where name =N'EmployeOrdersCount' And Type In ('S','U'))
DROP TABLE EmployeOrdersCount
ELSE
CREATE TABLE EmployeOrdersCount --员工订单统计
(
Id INT PRIMARY KEY IDENTITY,--主键ID
EmployeNO NVARCHAR(15), --员工编号
OrdersCount INT, --订单数量
)
INSERT INTO EmployeOrdersCount(EmployeNO,OrdersCount)
VALUES('100',100),('102',100),('103',100),('104',100),
('105',100),('106',99),('107',99),('108',99),('109',98),
('110',98),('111',97),('112',96),('113',100)
SELECT ROW_NUMBER() OVER(ORDER BY OrdersCount desc) AS 'RowNumber',
RANK() OVER(ORDER BY OrdersCount desc) AS 'Rank',
DENSE_RANK() OVER(ORDER BY OrdersCount desc) AS 'Dense_rank',
NTILE(4) OVER(ORDER BY OrdersCount desc) AS 'ntile'
,EmployeNO, OrdersCount
FROM EmployeOrdersCount
RowNumber Rank Dense_rank ntile EmployeNO OrdersCount
-------------------- -------------------- -------------------- -------------------- --------------- -----------
1 1 1 1 100 100
2 1 1
IF EXISTS (Select * From sys.objects Where name =N'EmployeOrdersCount' And Type In ('S','U'))
ELSE
SELECT ROW_NUMBER() OVER(ORDER BY OrdersCount desc) AS 'RowNumber',
RowNumber
-------------------- -------------------- -------------------- -------------------- --------------- -----------
1
2