使用 Access SQL 进行分组排名

Use Access SQL to do a grouped ranking

提问by PowerUser

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

我如何按部门分组的 # 个客户（包括关系）对销售人员进行排名？

For example, given this table, I want to create the Rank column on the right. How should I do this in Access?

例如，给定这个表，我想在右侧创建 Rank 列。我应该如何在 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

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.

我已经知道如何使用此 SQL 代码进行简单的排名。但我不知道如何重新设计以接受分组。

Select Count(*) from [Tbl] Where [#Customers] <  [Tblx]![#Customers] )+1

Also, there's plenty of answers for this using SQL Server's Rank() function, but I need to do this in Access. Suggestions, please?

此外，使用 SQL Server 的 Rank() 函数对此有很多答案，但我需要在 Access 中执行此操作。请给点建议？

回答by Paul Abbott

SELECT *, (select count(*) from tbl as tbl2 where
tbl.customers > tbl2.customers and tbl.dept = tbl2.dept) + 1 as rank from tbl

Just add the dept field to the subquery...

只需将 dept 字段添加到子查询中...

回答by Bas Volkers

Great solution with subquery! Except for huge recordsets, the subquery solution gets very slow. Its better(quicker) to use a Self JOIN, look at the folowing solution: self join

很好的子查询解决方案！除了巨大的记录集，子查询解决方案变得非常缓慢。使用 Self JOIN 更好（更快），请查看以下解决方案：self join

SELECT tbl1.SalesPerson , count(*) AS Rank 
FROM tbl AS tbl1 INNER JOIN tbl AS tbl2 ON tbl1.DEPT = tbl2.DEPT 
    AND tbl1.#Customers < tbl2.#Customers 
GROUP BY tbl1.SalesPerson 

回答by asonofTheAlmighty

I know this is an old thread. But since I spent a great deal of time on a very similar problem and was greatly helped by the former answers given here, I would like to share what I have found to be a MUCH faster way. (Beware, it is more complicated.)

我知道这是一个旧线程。但是由于我在一个非常相似的问题上花费了大量时间并且在这里给出的前一个答案极大地帮助了我，我想分享我发现的更快的方法。（当心，它更复杂。）

First make another table called "Individualizer". This will have one field containing a list of numbers 1 through the-highest-rank-that-you-need.

首先制作另一个名为“Individualizer”的表。这将有一个字段，其中包含从 1 到您需要的最高等级的数字列表。

Next create a VBA module and paste this into it:

接下来创建一个 VBA 模块并将其粘贴到其中：

'Global Declarations Section.
Option Explicit
Global Cntr

'*************************************************************
' Function:  Qcntr()
'
' Purpose: This function will increment and return a dynamic
' counter. This function should be called from a query.
'*************************************************************

Function QCntr(x) As Long
   Cntr = Cntr + 1
   QCntr = Cntr
End Function

'**************************************************************
' Function:  SetToZero()
'
' Purpose: This function will reset the global Cntr to 0. This
' function should be called each time before running a query
' containing the Qcntr() function.
'**************************************************************

Function SetToZero()
   Cntr = 0
End Function

Save it as Module1.

将其另存为 Module1。

Next, create Query1 like this:

接下来，像这样创建 Query1：

SELECT Table1.Dept, Count(Table1.Salesperson) AS CountOfSalesperson
FROM Table1
GROUP BY Table1.Dept;

Create a MakeTable query called Query2 like this:

创建一个名为 Query2 的 MakeTable 查询，如下所示：

SELECT SetToZero() AS Expr1, QCntr([ID]) AS Rank, Query1.Dept, 
Query1.CountOfSalesperson, Individualizer.ID 
INTO Qtable1
FROM Query1 
INNER JOIN Individualizer 
   ON Query1.CountOfSalesperson >= Individualizer.ID;

Create another MakeTable query called Query3 like this:

创建另一个名为 Query3 的 MakeTable 查询，如下所示：

SELECT SetToZero() AS Expr1, QCntr([Identifier]) AS Rank, 
[Salesperson] & [Dept] & [#Customers] AS Identifier, Table1.Salesperson, 
Table1.Dept, Table1.[#Customers] 
INTO Qtable2
FROM Table1;

If you have another field already that uniquely identifies every row you wouldn't need to create an Identifier field.

如果您已经有另一个字段来唯一标识每一行，则不需要创建标识符字段。

Run Query2 and Query3 to create the tables. Create a fourth query called Query4 like this:

运行 Query2 和 Query3 以创建表。创建名为 Query4 的第四个查询，如下所示：

SELECT Qtable2.Salesperson, Qtable2.Dept, Qtable2.[#Customers], Qtable1.ID AS Rank
FROM Qtable1 
INNER JOIN Qtable2 ON Qtable1.Rank = Qtable2.Rank;

Query4 returns the result you are looking for.

Query4 返回您要查找的结果。

Practically, you would want to write a VBA function to run Query2 and Query3 and then call that function from a button placed in a convenient location.

实际上，您可能希望编写一个 VBA 函数来运行 Query2 和 Query3，然后从放置在方便位置的按钮调用该函数。

Now I know this sounds ridiculously complicated for the example you gave. But in real life, I am sure your table is more complicated than this. Hopefully my examples can be applied to your actual situation. In my database with over 12,000 records this method is by FAR the fastest (as in: 6 seconds with 12,000 records compared to over 1 minute with 262 records ranked with the subquery method).

现在我知道对于你给出的例子来说这听起来非常复杂。但在现实生活中，我相信你的桌子比这更复杂。希望我的例子可以适用于您的实际情况。在我拥有超过 12,000 条记录的数据库中，此方法是 FAR 最快的（例如：12,000 条记录需要 6 秒，而使用子查询方法排列的 262 条记录需要超过 1 分钟）。

The real secret for me was the MakeTable query because this ranking method is useless unless you immediately output the results to a table. But, this does limit the situations that it can be applied to.

对我来说真正的秘密是 MakeTable 查询，因为除非您立即将结果输出到表格中，否则这种排名方法是没有用的。但是，这确实限制了它可以应用于的情况。

P.S. I forgot to mention that in my database I was not pulling results directly from a table. The records had already gone through a string of queries and multiple calculations before they needed to be ranked. This probably contributed greatly to the huge difference in speed between the two methods in my situation. If you are pulling records directly from a table, you might not notice nearly as big an improvement.

PS我忘了提到在我的数据库中我没有直接从表格中提取结果。这些记录在需要进行排名之前已经经过了一系列查询和多次计算。在我的情况下，这可能极大地导致了两种方法之间的速度差异。如果您直接从表中提取记录，您可能不会注意到几乎同样大的改进。

回答by Hannington Mambo

I normally pick tips and ideas from here and sometimes end up building amazing things from it!

我通常从这里挑选技巧和想法，有时最终会从中构建出令人惊叹的东西！

Today, (well let's say for the past one week), I have been tinkering with Ranking of data in Access and to the best of my ability, I did not anticipate what I was going to do something so complex as to take me a week to figure it out! I picked titbits from two main sites:

今天，（假设过去一周），我一直在修补 Access 中的数据排名，并尽我所能，我没有预料到我会做一些如此复杂的事情，以至于花了我一周的时间弄明白！我从两个主要站点中挑选了一些花絮：

1. https://usefulgyaan.wordpress.com/2013/04/23/ranking-in-ms-access/(seen that clever ‘>=' part, and the self joins? Amazing… it helped me to build my solution from just one query, as opposed to the complex method suggested above by asonoftheMighty (not discrediting you… just didn't want to try it for now; may be when I get to large data I might want to try that as well…)
2. Right here, from Paul Abott above ( ‘and tbl.dept = tbl2.dept')… I was lost after ranking because I was placing AND YearID = 1, etc, then the ranking would end up happening only for sub-sets, you guessed right, when YearID = 1! But I had a lot of different scenarios…

1. https://usefulgyaan.wordpress.com/2013/04/23/ranking-in-ms-access/（看到那个聪明的 '>=' 部分，和 self 连接？太神奇了......它帮助我从仅仅构建我的解决方案一个查询，与上面由 asonoftheMighty 建议的复杂方法相反（不是在诋毁你……只是暂时不想尝试；可能当我遇到大数据时，我可能也想尝试……）
2. 就在这里，来自上面的 Paul Abott（'and tbl.dept = tbl2.dept'）......我在排名后迷路了，因为我放置了 AND YearID = 1，等等，那么排名最终只会发生在子集上，你猜对了，当 YearID = 1 时！但我有很多不同的场景……

Well, I gave that story partly to thank the contributors mentioned, because what I did is to me one of the most complex of the ranking that I think can help you in almost any situation, and since I benefited from others, I would like to share here what I hope may benefit others as well.

嗯，我讲这个故事部分是为了感谢提到的贡献者，因为我所做的是最复杂的排名之一，我认为几乎可以在任何情况下为您提供帮助，而且由于我从其他人中受益，我想在这里分享我希望也可以使其他人受益的内容。

Forgive me that I am not able to post my table structures here, it is a lot of related tables. I will only post the query, so if you need to you may develop your tables to end up with that kind of query. But here is my scenario:

请原谅我不能在这里发布我的表格结构，这是很多相关的表格。我只会发布查询，因此如果您需要，您可以开发您的表以结束这种查询。但这是我的场景：

You have students in a school. They go through class 1 to 4, can either be in stream A or B, or none when the class is too small. They each take 4 exams (this part is not important now), so you get the total score for my case. That's it. Huh??

你有一所学校的学生。他们经历了 1 到 4 类，可以在流 A 或 B 中，或者当类太小时没有。他们每个人都参加了 4 门考试（这部分现在不重要了），所以你得到了我案例的总分。就是这样。嗯？？

Ok. Lets rank them this way:

好的。让我们这样排列它们：

We want to know the ranking of

我们想知道排名

? all students who ever passed through this school (best ever student)

? 所有通过这所学校的学生（有史以来最好的学生）

? all students in a particular academic year (student of the year)

? 特定学年的所有学生（年度学生）

? students of a particular class (but remember a student will have passed through all classes, so basically his/her rank in each of those classes for the different years) this is the usual ranking that appears in report cards

? 特定班级的学生（但请记住，学生将通过所有班级，因此基本上他/她在不同年份的每个班级中的排名）这是成绩单中出现的通常排名

? students in their streams (above comment applies)

? 学生在他们的信息流中（以上评论适用）

? I would also like to know the population against which we ranked this student in each category

? 我还想知道我们在每个类别中对该学生进行排名的人群

… all in one table/query. Now you get the point?

... 全部在一个表/查询中。现在你明白了吗？

(I normally like to do as much of my 'programming' in the database/queries to give me visuals and to reduce the amount of code I will later have to right. I actually won't use this query in my application :), but it let's me know where and how to send my parameters to the query it came from, and what results to expect in my rdlc)

（我通常喜欢在数据库/查询中进行尽可能多的“编程”，以便为我提供视觉效果并减少我以后必须正确处理的代码量。我实际上不会在我的应用程序中使用此查询 :)，但它让我知道在哪里以及如何将我的参数发送到它来自的查询，以及在我的 rdlc 中会得到什么结果）

Don't you worry, here it is:

别着急，这里有：

SELECT Sc.StudentID, Sc.StudentName, Sc.Mark, 
(SELECT COUNT(Sch.Mark) FROM [StudentScoreRankTermQ] AS Sch WHERE (Sch.Mark >= Sc.Mark)) AS SchoolRank, 
(SELECT Count(s.StudentID) FROM StudentScoreRankTermQ AS s) As SchoolTotal, 
(SELECT COUNT(Yr.Mark) FROM [StudentScoreRankTermQ] AS Yr WHERE (Yr.Mark >= Sc.Mark) AND (Yr.YearID = Sc.YearID) ) AS YearRank, 
(SELECT COUNT(StudentID) FROM StudentScoreRankTermQ AS Yt WHERE (Yt.YearID = Sc.YearID) ) AS YearTotal, 
(SELECT COUNT(Cl.Mark) FROM [StudentScoreRankTermQ] AS Cl WHERE (Cl.Mark >= Sc.Mark) AND (Cl.YearID = Sc.YearID) AND (Cl.TermID = Sc.TermID)  AND (Cl.ClassID=Sc.ClassID)) AS ClassRank, 
(SELECT COUNT(StudentID) FROM StudentScoreRankTermQ AS C WHERE (C.YearID = Sc.YearID) AND (C.TermID = Sc.TermID) AND (C.ClassID = Sc.ClassID) ) AS ClassTotal, 
(SELECT COUNT(Str.Mark) FROM [StudentScoreRankTermQ] AS Str WHERE (Str.Mark >= Sc.Mark)  AND (Str.YearID = Sc.YearID) AND (Str.TermID = Sc.TermID)  AND (Str.ClassID=Sc.ClassID) AND (Str.StreamID = Sc.StreamID) ) AS StreamRank, 
(SELECT COUNT(StudentID) FROM StudentScoreRankTermQ AS St WHERE (St.YearID = Sc.YearID) AND (St.TermID = Sc.TermID)  AND (St.ClassID = Sc.ClassID) AND (St.StreamID = Sc.StreamID) ) AS StreamTotal, 
Sc.CalendarYear, Sc.Term, Sc.ClassNo, Sc.Stream, Sc.StreamID, Sc.YearID, Sc.TermID, Sc.ClassID
FROM StudentScoreRankTermQ AS Sc
ORDER BY Sc.Mark DESC;

You should get something like this:

你应该得到这样的东西：

+-----------+-------------+------+------------+-------------+----------+-----------+-----------+------------+------------+-------------+------+------+-------+--------+
| StudentID | StudentName | Mark | SchoolRank | SchoolTotal | YearRank | YearTotal | ClassRank | ClassTotal | StreamRank | StreamTotal | Year | Term | Class | Stream |
+-----------+-------------+------+------------+-------------+----------+-----------+-----------+------------+------------+-------------+------+------+-------+--------+
| 1         | Jane        | 200  | 1          | 20          | 2        | 12        | 1         | 9          | 1          | 5           | 2017 | I    | 2     | A      |
| 2         | Tom         | 199  | 2          | 20          | 1        | 12        | 3         | 9          | 1          | 4           | 2016 | I    | 1     | B      |
+-----------+-------------+------+------------+-------------+----------+-----------+-----------+------------+------------+-------------+------+------+-------+--------+

Use the separators |to reconstruct the result table

使用分隔符|重建结果表

Just an idea about the tables, each student will be related to a class. Each class relates to years. Each stream relates to a class. Each term relates to a year. Each exam relates to a term and student and a class and a year; a student can be in class 1A in 2016 and moves on to class 2b in 2017, etc…

只是关于表格的一个想法，每个学生都将与一个班级相关。每个类别都与年份有关。每个流都与一个类相关。每个术语与一年有关。每门考试都与一个学期、一个学生、一个班级和一年有关；一个学生可以在 2016 年进入 1A 班，然后在 2017 年进入 2b 班，等等……

Let me also add that this a beta result, I have not tested it well enough and I do not yet have an opportunity to create a lot of data to see the performance. My first glance at it told me that it is good. So if you find reasons or alerts you want to point my way, please do so in comments so I may keep learning!

我还要补充一点，这是一个测试结果，我还没有对其进行足够好的测试，我还没有机会创建大量数据来查看性能。我第一眼看到它就告诉我它很好。因此，如果您发现原因或警报要指出我的方向，请在评论中这样做，以便我继续学习！

回答by David T. Macknet

You need to do some math. I typically take advantage of the combination of a counter field and an "offset" field. You're aiming for a table which looks like this (#Customers isn't necessary, but will give you a visual that you're doing it properly):

你需要做一些数学运算。我通常利用计数器字段和“偏移”字段的组合。您的目标是一个看起来像这样的表格（#Customers 不是必需的，但会给您一个视觉效果，表明您正在正确地进行操作）：

SalesPerson Dept #Customers Ctr Offset
Bill        DeptA     20    1   1
Ted         DeptA     30    2   1
Jane        DeptA     40    3   1
Bill        DeptB     50    4   4
Mary        DeptB     60    5   4

So, to give rank, you'd do [Ctr]-[Offset]+1 AS Rank

所以，为了给排名，你会做 [Ctr]-[Offset]+1 AS Rank

1. build a table with SalesPerson, Dept, Ctr, and Offset
2. insert into that table, ordered by Deptand #Customers(so that they're all sorted properly)
3. Update Offsetto be the MIN(Ctr), grouping on Dept
4. Perform your math calculation to determine Rank
5. Clear out the table so you're ready to use it again next time.

1. 建一个表SalesPerson，Dept，Ctr，和Offset
2. 插入该表，按Dept和#Customers排序（以便它们都正确排序）
3. 更新Offset为MIN(Ctr)，分组于Dept
4. 进行数学计算以确定 Rank
5. 清空桌子，以便下次再次使用它。

回答by turtlkky

To add to this and any other related Access Ranking or Rank Tie Breaker how-tos for other versions of Access, ranking should not be performed on crosstab queries if your FROM clause happens to NOT contain a table but a query that is either a crosstab query or a query that contains within it elsewhere a crosstab query.

要添加到此以及任何其他相关 Access Ranking 或 Rank Tie Breaker 其他版本 Access 的操作方法，如果您的 FROM 子句碰巧不包含表但查询是交叉表查询，则不应在交叉表查询上执行排名或其他地方包含交叉表查询的查询。

The code referenced above where a SELECT statement within a SELECT statment is used (sub query),

上面引用的代码在 SELECT 语句中使用了 SELECT 语句（子查询），

 "SELECT *, (select count(*) from tbl as tbl2 where tbl.customers > tbl2.customers and tbl.dept = tbl2.dept) + 1 as rank from tbl"

will not work and will always fail expressing a error on portion of the code where "tbl.customers > tbl2.customers"cannot be found.

将不起作用，并且总是无法在无法找到“tbl.customers > tbl2.customers”的代码部分表达错误。

In my situation on a past project, I was referencing a query instead of a table and within that query I had referenced a crosstab query thus failing and producing an error. I was able to resolve this by creating a table from the crosstab query first, and when I referenced the newly created table in the FROM clause, it started working for me.

在过去的项目中，我引用的是查询而不是表，在该查询中我引用了交叉表查询，因此失败并产生错误。我能够通过首先从交叉表查询创建一个表来解决这个问题，当我在 FROM 子句中引用新创建的表时，它开始对我来说有效。

So in final, normally you can reference a query or table in the FROM clause of the SELECT statement as what was shared previously above to do ranking, but be carefull as to if you are referencing a query instead of a table, that query must Notbe a crosstab query or reference another query that is a crosstab query.

所以最后，通常你可以在 SELECT 语句的 FROM 子句中引用一个查询或表作为上面共享的内容来进行排名，但要小心，如果你引用的是查询而不是表，该查询必须不是是交叉表查询或引用另一个作为交叉表查询的查询。

Hope this helps anyone else that may have had problems looking for a possible reason if you happen to reference the statements above and you are not referencing a table in your FROM clause within your own project. Also, performing subqueries on aliases with crosstab queries in Access probably isn't good idea or best practice either so stray away from that if/when possible.

如果您碰巧引用了上面的语句并且您没有在自己的项目中的 FROM 子句中引用表，希望这可以帮助那些可能在寻找可能原因时遇到问题的其他人。此外，在 Access 中使用交叉表查询对别名执行子查询可能不是一个好主意或最佳实践，因此如果/在可能的情况下远离它。

If you found this useful, and wish that Access would allow the use of a scrolling mouse in a passthru query editor, give me a like please.

如果您发现这很有用，并希望 Access 允许在 passthru 查询编辑器中使用滚动鼠标，请给我一个赞。