在select语句中从SUM返回前5位

I need to return the following statement but I only want to return the TOP 5 of each Sale value only.....not all the records.

我需要返回以下声明,但我只想返回每个Sale值的TOP 5 .....并非所有记录。

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

I do not want this!

我不想要这个!

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

6 个解决方案

#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

#2

WITH TopSales AS (
  SELECT *, RANK() OVER (PARTITION BY ID, Code ORDER BY Sale DESC) saleRank
  FROM TableName
)
SELECT ID, Code, SUM(Sale) AS Sale
  FROM TopSales
  WHERE (Code = 11) AND (saleRank <= 5)
  GROUP BY ID, code

#3

select id, code, SUM (sale) 
from
(
    select id, code, sale, 
        ROW_NUMBER() over(partition by id, code order by sale desc) rn 
    from tablename
) v
where rn<=5
group by id, code

#4

Probably you need something like:

可能你需要这样的东西:

;WITH sales (
SELECT
 id, 
 code,
 sale,
 ROW_NUMBER() OVER (PARTITION BY id, code ORDER BY sales DESC) n
FROM
 TableName
WHERE
 Code = 11
)
SELECT
 id, code, sum(sale) sale
FROM
 sales
WHERE
 n <= 5
GROUP BY
 id,
 code

ROW_NUMBER() and PARTITION BY help to find last 5 sales. Then you SUM only top (highest) 5.

ROW_NUMBER()和PARTITION BY帮助查找最近5次销售。那么你只有SUM(最高)5。

This query returns sum of top 5 sales for each (id, code) group.

此查询返回每个(id,code)组的前5个销售额的总和。

#5

If you want to return just the top 5 results for each group you could do this:

如果您只想返回每组的前5个结果,您可以这样做:

with cte as 
(ID, Code, Sale,ROW_NUMBER() over(partition by  ID,
Code order by (select 0)) rownum
from TableName)
Select ID, Code, sum(Sale) as Sale from cte
Where Code = 11
and rownum<=5
Group By ID, code

If you want to return top 5 results with highest salary for each group you could do this:

如果您想要为每个组返回薪水最高的前5个结果,您可以这样做:

with cte as 
(ID, Code, Sale,ROW_NUMBER() over(partition by  ID,
Code order by Sale desc) rownum
from TableName)
Select ID, Code, sum(Sale) as Sale from cte
Where Code = 11
and rownum<=5
Group By ID, code

#6

-1

select id, code, sum(sale) as sale from tablename
where code = 11
group by id, code
order by sum(sale) desc
limit 5

#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

#2

WITH TopSales AS (
  SELECT *, RANK() OVER (PARTITION BY ID, Code ORDER BY Sale DESC) saleRank
  FROM TableName
)
SELECT ID, Code, SUM(Sale) AS Sale
  FROM TopSales
  WHERE (Code = 11) AND (saleRank <= 5)
  GROUP BY ID, code

#3

select id, code, SUM (sale) 
from
(
    select id, code, sale, 
        ROW_NUMBER() over(partition by id, code order by sale desc) rn 
    from tablename
) v
where rn<=5
group by id, code

#4