使用Access SQL进行分组排名 [英] Use Access SQL to do a grouped ranking
本文介绍了使用Access SQL进行分组排名的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何按部门划分的#em个客户对销售人员进行排名(包括联系)?
How do I rank salespeople by # customers grouped by department (with ties included)?
例如,给定此表,我想在右侧创建等级"列.我应该如何在Access中执行此操作?
销售人员部门#客户排名 Bill DeptA 20 1 特德DeptA 30 2 Jane DeptA 40 3 法案部B 50 1 玛丽部B 60 2For example, given this table, I want to create the Rank column on the right. How should I do this in Access?
SalesPerson Dept #Customers Rank Bill DeptA 20 1 Ted DeptA 30 2 Jane DeptA 40 3 Bill DeptB 50 1 Mary DeptB 60 2我已经知道如何使用此SQL代码进行简单排名.但是我不知道该如何重做以接受分组.
I already know how to do a simple ranking with this SQL code. But I don't know how to rework this to accept grouping.
Select Count(*) from [Tbl] Where [#Customers] < [Tblx]![#Customers] )+1
此外,使用SQL Server的Rank()函数对此有很多答案,但是我需要在Access中做到这一点.有建议吗?
Also, there's plenty of answers for this using SQL Server's Rank() function, but I need to do this in Access. Suggestions, please?
推荐答案
SELECT *, (select count(*) from tbl as tbl2 where tbl.customers > tbl2.customers and tbl.dept = tbl2.dept) + 1 as rank from tbl
只需将dept字段添加到子查询中...
Just add the dept field to the subquery...
这篇关于使用Access SQL进行分组排名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文