SQLServer2005新增序列功能的几个函数：ROW_NUMBER, RANK, DENSE_RANK, and NTILE_sql

概述SQLServer2005新增序列功能的几个函数：ROW_NUMBER, RANK, DENSE_RANK, and NTILE 1 ROW_NUMBER SELECT ProductID, Name, Price, ROW_NUMBER() OVER(ORDER BY Price DESC) As PriceRankFROM Products ProductID SQLServer2005新增序列功能的几个函数：ROW_NUMBER,RANK,DENSE_RANK,and NTILE 1 ROW_NUMBER

SELECT ProductID,name,Price,ROW_NUMBER() OVER(ORDER BY Price DESC) As PriceRankFROM Products

ProductID	name	Price	PriceRank
8	Desk	495.0000	1
10	Executive Chair	295.0000	2
9	Chair	125.0000	3
5	Mouse	14.9500	4
6	Mousepad	9.9900	5
11	Scissors	8.5000	6
4	Stapler	7.9500	7
3	Binder	1.9500	8
...

SELECT c.name,o.DateOrdered,tab.TotalOrderAmount,ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY TotalOrderAmount DESC) AS BestCustomerFROM vwTotalAmountBilledPerOrder AS tab        INNER JOIN Orders AS o ON            o.OrderID = tab.OrderID        INNER JOIN Customers AS c ON            c.CustomerID = o.CustomerID

name	DateOrdered	TotalOrderAmount	BestCustomer
Bob	12/1/2005	12649.9900	1
Bob	12/19/2005	265.8500	2
Tito	12/22/2005	14.9500	1
Tito	12/18/2005	12.4400	2
Darren	1/2/2006	620.0000	1
Bruce	1/5/2006	14.9500	1
Bruce	1/4/2006	9.9900	2
Lee Ann	1/3/2006	8.5000	1
...

2 RANK

SELECT c.name,RANK() OVER (ORDER BY TotalOrderAmount DESC) AS BestCustomerFROM vwTotalAmountBilledPerOrder AS tab        INNER JOIN Orders AS o ON            o.OrderID = tab.OrderID        INNER JOIN Customers AS c ON            c.CustomerID = o.CustomerID

name	DateOrdered	TotalOrderAmount	BestCustomer
Bob	12/1/2005	12649.9900	1
Darren	1/2/2006	620.0000	2
Bob	12/19/2005	265.8500	3
Tito	12/22/2005	14.9500	4
Bruce	1/5/2006	14.9500	4
Tito	12/18/2005	12.4400	6
Bruce	1/4/2006	9.9900	7
Lee Ann	1/3/2006	8.5000	8
...

3 NTILE

SELECT ProductID,NTILE(4) OVER (ORDER BY Price DESC) as Quartile
 FROM Produts

ProductID	name	Price	Quartile
8	Desk	495.0000	1
10	Executive Chair	295.0000	1
9	Chair	125.0000	2
5	Mouse	14.9500	2
6	Mousepad	9.9900	3
11	Scissors	8.5000	3
4	Stapler	7.9500	4
3	Binder	1.9500	4

总结

以上是内存溢出为你收集整理的SQLServer2005新增序列功能的几个函数：ROW_NUMBER, RANK, DENSE_RANK, and NTILE全部内容，希望文章能够帮你解决SQLServer2005新增序列功能的几个函数：ROW_NUMBER, RANK, DENSE_RANK, and NTILE所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/sjk/1182291.html

SQLServer2005新增序列功能的几个函数：ROW_NUMBER, RANK, DENSE_RANK, and NTILE

发表评论

评论列表（0条）