SQL查询以比较前5名销售人员的季度业绩
SQL查询以比较前5名销售人员的季度业绩
提问于 2015-02-23 04:20:48
我有从2003年Q3到2005年Q2销售人员订单数量的数据，我想显示每个季度的结果，销售人员和前5名销售人员的总订单数量。
我有一条sql语句
select DATEPART (Year, [Order Date]) AS Year,
DATEPART (QUARTER,[Order Date]) As Quarter,
Salesperson,
SUM([Order Amount]) As [Total Order Amount]
from dbo.tbl_sales
Group By DATEPART (YEAR, [Order Date]),
DATEPART (QUARTER, [Order Date]),
Salesperson
Order By SUM([Order Amount]) DESC但这不仅是显示前5名销售人员，请帮助。
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-02-23 04:41:09
我想你每季度要5英镑。试试这个：
select *
from (
select
T.Year, T.Quarter, T.Salesperson, T.[Total Order Amount],
rank() over (partition by T.Year, T.Quarter order by T.[Total Order Amount] desc) as rnk
from (
select
datepart(yy, [Order Date]) AS Year,
datepart(qq,[Order Date]) As Quarter,
Salesperson,
sum([Order Amount]) As [Total Order Amount]
from dbo.tbl_sales
group by
datepart(yy, [Order Date]),
datepart(qq, [Order Date]),
Salesperson
) as T
) as T2
where T2.rnk <= 5
order by T2.Year, T2.Quarter, T2.rnk, T2.Salesperson我，你想排名前5在所有季度，然后突破，只有少数人，然后试试这个。
with top5 as (
select Salesperson, rnk from (
select Salesperson, rank() over (order by sum([Order Amount]) desc) as rnk
from dbo.tbl_sales
group by Salesperson
) as T
where rnk <= 5
)
select
datepart(yy, [Order Date]) as yr,
datepart(qq, [Order Date]) as qtr,
s.Salesperson,
sum([Order Amount]) As [Quarterly Order Amount]
from
dbo.tbl_sales as s inner join
top5 on top5.Salesperson = s.Salesperson
group by
datepart(yy, [Order Date]),
datepart(qq, [Order Date]),
s.Salesperson
order by
yr,
qtr更清楚地说明你需要什么是有帮助的。正如您所看到的，这两个查询是完全不同的。在看了你发的截图后，我不确定这就是你想要的答案。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28666850
复制相关文章
相似问题
腾讯云开发者
