我想知道是否有一种方法可以在汇总结果上显示多个列,但又不影响分组依据。
我需要在汇总结果旁边显示名称,但是我不知道我在这里缺少什么。
这是我正在使用的数据:
它是以下查询的结果:
select * from Salesman, Sale,Buyer
where Salesman.ID = Buyer.Salesman_ID and Buyer.ID = sale.Buyer_ID
我需要找到特定年份销售最多的东西(总价)的推销员。
这是我到目前为止的内容:
select DATEPART(year,sale.sale_date)'year', Salesman.First_Name,sum(sale.price)
from Salesman, Sale,Buyer
where Salesman.ID = Buyer.Salesman_ID and Buyer.ID = sale.Buyer_ID
group by DATEPART(year,sale.sale_date),Salesman.First_Name
这将返回每个销售员的总销售额。
我如何从这里继续获得每年的最佳销售员?
也许我正在执行的查询是完全错误的,并且有更好的方法?
任何意见将是有益的。
谢谢。
这应该为您工作:
select *
from(
select DATEPART(year,s.sale_date) as SalesYear -- Avoid reserved words for object names
,sm.First_Name
,sum(s.price) as TotalSales
,row_number() over (partition by DATEPART(year,s.sale_date) -- Rank the data within the same year as this data row.
order by sum(s.price) desc -- Order by the sum total of sales price, with the largest first (Descending). This means that rank 1 is the highest amount.
) as SalesRank -- Orders your salesmen by the total sales within each year, with 1 as the best.
from Buyer b
inner join Sale s
on(b.ID = s.Buyer_ID)
inner join Salesman sm
on(sm.ID = b.Salesman_ID)
group by DATEPART(year,s.sale_date)
,sm.First_Name
) a
where SalesRank = 1 -- This means you only get the top salesman for each year.
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句