SQL 2005的行号(转)

最新推荐文章于 2022-02-25 18:19:43 发布

最新推荐文章于 2022-02-25 18:19:43 发布 · 168 阅读

文章标签：

#SQL #C #C++ #C#

本文介绍并演示了SQL中ROW_NUMBER(), RANK() 和 NTILE()等窗口函数的应用案例。通过具体场景展示了如何使用这些函数进行数据排名、分组及分区处理。

select CAST(ROW_NUMBER() OVER(order by Name) AS varchar(32)) AS rowindex可以将行号取出来转换成varchar型的显示在页面上

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