MS Access SQL中的查询排名

介绍

通常会要求对结果进行排名或编号的查询。 在功能更强大的数据库管理系统（例如Microsoft SQL和DB2）中，通常具有执行此操作的功能。 但是，在Microsoft Access中，不存在这样的功能。

选件

在Access中，有几种解决方法：

1. 报告中的累计金额
2. SELECT子句中调用的VBA代码
3. SELECT子句中的子查询
4. SELECT子句中的DCount
5. 将表连接到自身并使用COUNT

如果要连续对行编号，则选项1可能是最简单的，但在选项中灵活性最低。

选项2、3和4要求每行分别进行评估，对于大型数据集可能会比较慢。

选项5是最难理解的，但通常效率最高。 这是我将在本文中讨论的选项。

例子

给出下表和数据：

ID Salesperson Division    NumberSold
1  Robert      Electronics 99
2  Jenny       Electronics 54
3  Billy       Appliances  54
4  Karen       Appliances  102
5  Kim         Appliances  30

对于第一个示例，假设您要按已售出的商品数量对所有推销员进行排名，您可以将表格与已售出的商品数量结合起来并进行计数。

询问

SELECT 
   t1.Salesperson,
   t1.Division,
   t1.NumberSold,
   COUNT(*) + 1 AS Rank
FROM
   tblSales AS t1 
   LEFT JOIN tblSales AS t2
      ON t1.NumberSold < t2.NumberSold
GROUP BY
   t1.Salesperson,
   t1.Division,
   t1.NumberSold

结果

Salesperson Division    NumberSold Rank
Robert      Electronics 99         2
Jenny       Electronics 54         3
Billy       Appliances  54         3
Karen       Appliances  102        1
Kim         Appliances  30         5

注意，这给平局相同的等级。 如果要对行编号而不是对行进行排名，则需要使用唯一字段。

询问

SELECT 
   t1.Salesperson,
   t1.Division,
   t1.NumberSold,
   COUNT(*) AS Rank
FROM
   tblSales AS t1 
   LEFT JOIN tblSales AS t2
      ON t1.NumberSold < t2.NumberSold OR
         (t1.NumberSold = t2.NumberSold AND
         t1.ID <= t2.ID)
GROUP BY
   t1.Salesperson,
   t1.Division,
   t1.NumberSold

结果

Salesperson Division    NumberSold Rank
Robert      Electronics 99         2
Jenny       Electronics 54         4
Billy       Appliances  54         3
Karen       Appliances  102        1
Kim         Appliances  30         5

如果要按分组字段划分排名或编号，可以通过将它们包括在JOIN子句中来实现。

询问

SELECT 
   t1.Salesperson,
   t1.Division,
   t1.NumberSold,
   COUNT(*) AS Rank
FROM
   tblSales AS t1 
   LEFT JOIN tblSales AS t2
      ON t1.Division = t2.Division AND
         t1.NumberSold <= t2.NumberSold
GROUP BY
   t1.Salesperson,
   t1.Division,
   t1.NumberSold

结果

Salesperson Division    NumberSold Rank
Robert      Electronics 99         1
Jenny       Electronics 54         2
Billy       Appliances  54         2
Karen       Appliances  102        1
Kim         Appliances  30         3

请注意，此排名从最高到最低。 从最低到最高仅需要将小于运算符转换为大于运算符。

翻译自: https://bytes.com/topic/access/insights/954764-ranking-queries-ms-access-sql