使用 Access SQL 进行分组排名 [英] Use Access SQL to do a grouped ranking

问题描述

我如何根据 # 个客户对销售人员进行排名按部门分组(包括关系)?

How do I rank salespeople by # customers grouped by department (with ties included)?

例如，给定这个表，我想在右边创建Rank列.我应该如何在 Access 中执行此操作?<前>销售部 #Customers Rank账单部门A 20 1泰德部门 30 2简部门A 40 3账单部门B 50 1玛丽部门B 60 2

For 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屋！