sql - 在 select 语句中从 SUM 返回前 5 个

Question

我需要返回以下语句，但我只想返回每个销售值的前 5 名......不是所有记录。

Select ID, Code, sum(Sale) as Sale from TableName
Where Code = 11
Group By ID, code

我不想要这个!

Select TOP 5 ID, Code, sum(Sale) as Sale from TableName
Where Code = 11
Group By ID, code

Answer 1

With Cte as 
( Select  ID, Code, sale as Sales ,
  row_number() over (partition by ID,code order by sale desc) as row_num 
  from TableName where code=11
)
Select Id,code,sum(sales) from cte 
GROUP BY ID, code
WHERE row_num < 6