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

最新推荐文章于 2023-11-21 14:05:14 发布

转载最新推荐文章于 2023-11-21 14:05:14 发布 · 128 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/zhoujg/archive/2006/03/23/357092.html

文章标签：

#数据库

本文深入探讨了SQL窗口函数ROW_NUMBER(), RANK() 和 NTILE()的实际应用案例，展示了如何使用这些函数进行数据排名和分组，包括产品价格排名、客户消费总额排名以及商品价格四分位数划分。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 ROW_NUMBER

SELECT ProductID, Name, Price, 
       ROW_NUMBER() OVER(ORDER BY Price DESC) As PriceRank
FROM 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 BestCustomer
FROM 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, o.DateOrdered, tab.TotalOrderAmount, 
       RANK() OVER (ORDER BY TotalOrderAmount DESC) AS BestCustomer
FROM 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, Name, Price, 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