使用Access SQL执行分组排名-Java 学习之路

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

例如，给定此表，我想在右侧创建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

我已经知道如何使用这个SQL代码进行简单的排名 . 但我不知道如何重做这个来接受分组 .

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

此外，使用SQL Server的Rank（）函数有很多答案，但我需要在Access中执行此操作 . 建议好吗？

6 回答

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

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

回复于 2024-04-24T12:27:53+08:00

2
我知道这是一个老话题 . 但是由于我花了很多时间在一个非常相似的问题上并且在这里给出的前答案得到很大帮助，我想分享我发现的更快的方法 . （小心，它更复杂 . ）

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

接下来创建一个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
```
将其另存为Module1 .

接下来，像这样创建Query1：
```
SELECT Table1.Dept, Count(Table1.Salesperson) AS CountOfSalesperson
FROM Table1
GROUP BY Table1.Dept;
```
创建一个名为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;
```
创建另一个名为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;
```
如果您已经有另一个字段唯一标识每一行，则不需要创建标识符字段 .

运行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返回您要查找的结果 .

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

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

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

附：我忘了提到在我的数据库中我没有直接从表中提取结果 . 记录在需要排名之前已经经历了一系列查询和多次计算 . 这可能极大地促成了我的情况下两种方法之间的巨大速度差异 . 如果您直接从表中提取记录，您可能不会注意到几乎同样大的改进 .
回复于 2024-04-24T12:27:53+08:00
13
你需要做一些数学运算 . 我通常利用计数器字段和“偏移”字段的组合 . 你的目标是一个看起来像这样的 table （#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
```
所以，为了给出排名，你要做[Ctr] - [Offset] 1 AS Rank
- 使用 SalesPerson ， Dept ， Ctr 和 Offset 构建一个表
- 插入到该表中，由 Dept 和 #Customers 排序（以便它们全部排序正确）
- 更新 Offset 为 MIN(Ctr) ，分组 Dept
- 执行数学计算以确定 Rank
- 清除表格，以便下次再准备好再使用它 .
回复于 2024-04-24T12:27:53+08:00
0
我通常从这里挑选技巧和想法，有时最终会从中构建出令人惊奇的东西！

今天（好吧，比如说过去一周），我一直在修补Access中的数据排名，并尽我所能，我没想到我会做一些如此复杂的事情，以便带我一周弄明白！我从两个主要网站上挑选了一些东西：
- https://usefulgyaan.wordpress.com/2013/04/23/ranking-in-ms-access/（看到聪明的'> ='部分，自我加入？太棒了......它帮助我从一个查询构建我的解决方案，而不是上面提到的asonoftheMighty的复杂方法（没有诋毁你......只是没有我想暂时尝试一下;也许当我获得大数据时，我可能也想尝试一下......）
- 就在这里，来自上面的保罗·阿博特（'和tbl.dept = tbl2.dept'）...我在排名后输了因为我放置了AND YearID = 1等等，那么排名最终只会发生在子集中，你猜对了，年份ID = 1！但我有很多不同的场景......
好吧，我给出了这个故事，部分是为了感谢所提到的贡献者，因为我所做的对我来说是最复杂的排名，我认为几乎可以在任何情况下帮助你，而且因为我从其他人那里受益，我想在这里分享我希望可能对其他人有益的东西 .

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

你在学校有学生 . 它们通过1到4级，可以是A流或B流，也可以是类太小 . 他们每人参加4次考试（这部分现在并不重要），所以你得到了我的案例总分 . 而已 . 咦？

好 . 让我们这样排名：

我们想知道排名

•所有通过该学校的学生（有史以来最好的学生）

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

•特定 class 的学生（但请记住，学生将通过所有 class ，因此基本上他/她在不同年份的每个 class 中的等级）这是报告卡中出现的通常排名

•学生在他们的溪流中（以上评论适用）

•我还想知道我们在每个类别中对这个学生进行排名的人口

...所有在一个表/查询中 . 现在你明白了吗？

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

你不担心，这里是：
```
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;
```
你应该得到这样的东西：

StudentID | StudentName |马克| SchoolRank | SchoolTotal | YearRank | YearTotal | ClassRank | ClassTotal | StreamRank | StreamTotal |年|条款|类|流

1 |简| 200 | 1 | 20 | 2 | 12 | 1 | 9 | 1 | 5 | 2017年| I | 2 | A

2 |汤姆| 199 | 2 | 20 | 1 | 12 | 3 | 9 | 1 | 4 | 2016 | I | 1 |乙

使用分隔符（|）重建结果表

只是关于表格的想法，每个学生将与一个 class 相关 . 每个 class 都与年份有关 . 每个流都与一个类有关 . 每个学期与一年有关 . 每个考试涉及学期和学生，一个 class 和一年;学生可以在2016年进入1A级，并在2017年进入2b级等...

我还要补充一点，这是一个测试结果，我还没有很好地测试它，我还没有机会创建大量数据来查看性能 . 我第一眼看到它告诉我这很好 . 因此，如果您找到想要指明的原因或提醒，请在评论中这样做，以便我继续学习！
回复于 2024-04-24T12:27:53+08:00
1
子查询的绝佳解决方案！除了巨大的记录集，子查询解决方案变得非常慢 . 使用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
```
回复于 2024-04-24T12:27:53+08:00
0
要为其他版本的Access添加此和任何其他相关的访问排名或Rank Tie Breaker how-tos，如果您的FROM子句碰巧不包含表但是查询是交叉表查询，则不应对交叉表查询执行排名或者在其他地方包含交叉表查询的查询 .

上面引用的代码，其中使用SELECT语句中的SELECT语句（子查询），
```
"SELECT *, (select count(*) from tbl as tbl2 where tbl.customers > tbl2.customers and tbl.dept = tbl2.dept) + 1 as rank from tbl"
```
将无法工作，并始终无法在代码中找不到“tbl.customers> tbl2.customers ”的部分表示错误 .

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

所以在最后，通常你可以在SELECT语句的FROM子句中引用一个查询或表作为上面之前共享的排序，但要注意如果你引用的是查询而不是表，那查询必须 Not 是交叉表查询或引用作为交叉表查询的另一个查询 .

希望这可以帮助其他任何可能遇到问题的人如果您碰巧引用上述语句并且您没有在您自己的项目中引用FROM子句中的表 . 此外，在Access中使用交叉表查询对别名执行子查询可能不是一个好主意或最佳实践，因此如果/可能的话，就会偏离它 .

如果您发现这很有用，并希望Access允许在passthru查询编辑器中使用滚动鼠标，请给我一个喜欢 .
回复于 2024-04-24T12:27:53+08:00

使用Access SQL执行分组排名

6 回答

相关问题