SQL Server，使用行号

Question

>我有以下查询，它返回按售出单位数量排序的已售商品列表。销量最高的商品排名第 1，然后后续商品按升序排名。

SELECT  
RANK() OVER (ORDER BY SUM(Quantity) DESC,       
SUM(LineTotalInDefaultCurrency) DESC) AS SalesRank,
P.Name ,SUM(Quantity) as UnitsSold ,
SUM(LineTotalInDefaultCurrency) as RevenueDefaultCurrency, 
sf.ProductId
FROM 
SalesFact sf 
INNER JOIN 
Product p ON sf.ProductId = p.ProductId 
WHERE 
Dttm >= '2014-08-08' AND Dttm <= '2017-08-09' GROUP BY sf.ProductId, p.Name

它返回如下结果列表

SalesRank   Name                                UnitsSold   RevenueDefaultCurrency  ProductId   
1           Energy Saving Dryer Balls           1230        6429.58         1086381 
2           Universal Dishwasher Cutlery Basket 654         4700.64         1107301 
3           Limescale and Detergent Remover     361         4106.00         664212  
4           Universal Extendable Oven Shelf     364         3885.77         655005  
5           2500 Watt Fan Oven Element          157         1532.72         1019719 
6           Filter Vacuum Bags NVM-1CH          273         2320.88         479302  
7           Universal Dishwasher Cutlery Basket 81          1954.66         511673  
8           Ice Cube Tray                       10          20.99           655045
8           Vacuum Filter - Pack of 2           10          20.99           470556
8           Vacuum Post Motor Filter            10          20.99           1562181

我正在尝试向生成的查询添加行号，以便我的结果看起来像

Row SalesRank   Name                                UnitsSold   RevenueDefaultCurrency  ProductId   
1   1           Energy Saving Dryer Balls           1230        6429.58         1086381     
2   2           Universal Dishwasher Cutlery Basket 654         4700.64         1107301     
3   3           Limescale and Detergent Remover     361         4106.00         664212      
4   4           Universal Extendable Oven Shelf     364         3885.77         655005  
5   5           2500 Watt Fan Oven Element          157         1532.72         1019719 
6   6           Filter Vacuum Bags NVM-1CH          273         2320.88         479302  
7   7           Universal Dishwasher Cutlery Basket 81          1954.66         511673  
8   8           Ice Cube Tray                       10          20.99           655045
9   8           Vacuum Filter - Pack of 2           10          20.99           470556
10  8           Vacuum Post Motor Filter            10          20.99           1562181

我一直在尝试使用 ROW_NUMBER(( 来实现这一点。目前我已经修改了我的查询以包含 ROW_NUMBER((，所以我现在有

SELECT  
RANK() OVER (ORDER BY SUM(Quantity) DESC, 
SUM(LineTotalInDefaultCurrency) DESC) AS SalesRank,
p.Name ,SUM(Quantity) as UnitsSold ,
SUM(LineTotalInDefaultCurrency) as RevenueDefaultCurrency,      
sf.ProductId,
ROW_NUMBER() OVER(ORDER BY COUNT(sf.ProductId) DESC, sf.ProductId) AS [row]
FROM 
SalesFact sf INNER JOIN Product p ON sf.ProductId = p.ProductId 
WHERE 
Dttm >= '2014-08-08' AND Dttm <= '2017-08-09' 
GROUP BY 
sf.ProductId, p.Name

但是我无法正确订购。如果我添加 ORDER BY 行，则 SalesRank 是无序的，如果我按 SalesRank 添加 ORDER，则结果不会按行排序

我希望这是有道理的。谁能建议我如何获得像上面这样的结果集。 谢谢

Answer 1

我认为您的row定义是错误的：

SELECT ROW_NUMBER() OVER (ORDER BY SUM(Quantity) DESC, 
SUM(LineTotalInDefaultCurrency) DESC
) AS row,
RANK() OVER (ORDER BY SUM(Quantity) DESC, 
SUM(LineTotalInDefaultCurrency) DESC
) AS SalesRank,
p.Name, SUM(Quantity) as UnitsSold ,
SUM(LineTotalInDefaultCurrency) as RevenueDefaultCurrency,      
sf.ProductId,   
FROM SalesFact sf INNER JOIN
Product p
ON sf.ProductId = p.ProductId 
WHERE Dttm >= '2014-08-08' AND Dttm <= '2017-08-09' 
GROUP BY sf.ProductId, p.Name
ORDER BY [row];

Answer 2

从您想要的结果集中，您似乎希望对row_number使用相同的顺序，但在同一等级中按名称进行附加顺序：

with cte as
(
SELECT  
p.Name ,
SUM(Quantity) as UnitsSold ,
SUM(LineTotalInDefaultCurrency) as RevenueDefaultCurrency,  
sf.ProductId
FROM 
SalesFact sf 
INNER JOIN 
Product p ON sf.ProductId = p.ProductId 
WHERE 
Dttm >= '2014-08-08' AND Dttm <= '2017-08-09' 
GROUP BY 
sf.ProductId, p.Name
)
select *,    
RANK() OVER (ORDER BY UnitsSold DESC, RevenueDefaultCurrency DESC) AS SalesRank,
ROW_NUMBER() OVER (ORDER BY UnitsSold DESC, RevenueDefaultCurrency desc, Name ) AS [row] 
from cte;