access排名_在Microsoft Access中对行进行排名

access排名

This is the third article on row numbers in Microsoft Access.

这是有关Microsoft Access中行号的第三篇文章。

The first is about Random Rows in Microsoft Access.

首先是关于Microsoft Access中的随机行 。

The second is about Sequential Rows in Microsoft Access.

第二个关于Microsoft Access中的顺序行 。

排名如何？ (What to rank?)

Many things can be ranked, for example: 

可以对许多事情进行排名，例如：

• sales results
  销售业绩
• prices for a product from different vendors
  不同供应商的产品价格
• fuel consumption for vehicles
  车辆油耗
• sport results
  运动成绩
• eaten hamburgers per quarter
  每季度吃汉堡包

The principle of ranking is not difficult to understand, but one topic complicates matters: Duplicates.

排名原则并不难理解，但是一个主题使事情变得复杂：重复。

If any two or more of the values are equal, we must have clear guidelines prepared:

如果两个或两个以上的值相等，则必须准备明确的准则：

• how should the duplicates be ranked?
  重复项应如何排序？
• how should the duplicates influence the ranking of the succeeding values?
  重复项应如何影响后续值的排名？

Ranking values, taking this into account, can be done using any of five common methods also called strategies.

考虑到这一点，可以使用五种常见方法（也称为策略）中的任何一种来对值进行排名。

Also, the values to rank can be ordered ascending or descending. The default is descending, meaning that the highest values are assigned the highest (numerically lowest) ranks. However, ascending is also common and is what is used for "smallest is best" measures like time to run 100m or fuel consumed per distance unit.

同样，要排序的值可以升序或降序排列。 默认值为降序，这意味着最高值被赋予最高（数字最低）等级。 但是，升序也是常见的，它是“最小最好”测量方法，例如行驶100m的时间或每距离单位消耗的燃料。

The difference between ascending and descending rank can be illustrated by this small example:

这个小例子可以说明升序和降序之间的差异：

values:	3.4	5.1	2.6	7.3
Ascending	2	3	1	4
Descending	3	2	4	1

值：	3.4	5.1	2.6	7.3
上升	2	3	1个	4
降序	3	2	4	1个

排名策略 (Ranking strategies)

A list of these strategies with a detailed explanation for each can be found on WikiPedia: Ranking.

这些策略的列表以及每种策略的详细说明可以在WikiPedia ： 排名中找到。

Therefore, here we will only quote the list of strategies and the two matching formulas of Excel in case you are familiar with these:

因此，在这里我们仅引用策略列表和两个匹配的Excel公式，以防您熟悉以下内容：

Name	nick name	excel equivalent formula
Standard competition ranking	"1224" ranking	RANK.EQ
Modified competition ranking	"1334" ranking
Dense ranking	"1233" ranking
Ordinal ranking	"1234" ranking
Fractional ranking	"1 2.5 2.5 4" ranking	RANK.AVG

名称	昵称	excel等效公式
标准比赛排名	“ 1224”排名	排名
修改后的比赛排名	“ 1334”排名
密集排名	“ 1233”排名
顺序排名	“ 1234”排名
分数排名	“ 1 2.5 2.5 4”排名	排名

So, what strategy should you use? For some scenarios, a very strict strategy is either common or has been decided for you. For other scenarios - indeed those of your own - you must choose one. Here it can be of value to be acquainted with the main features for each of these, which are:

那么，您应该使用什么策略？ 在某些情况下，非常严格的策略要么很常见，要么已为​​您决定。 对于其他情况-实际上是您自己的情况-您必须选择一种。 在这里熟悉以下每个方面的主要功能可能很有价值：

strategy	main feature
Standard competition ranking	Identical values are ranked the same
Modified competition ranking	Identical values are ranked the same
Dense ranking	No gaps between the ranks
Ordinal ranking	All record numbers are present, thus all ranks are unique
Fractional ranking	The sum of the ranks is the same as under ordinal ranking

战略	主要特征
标准比赛排名	相同的值排名相同
修改后的比赛排名	相同的值排名相同
密集排名	队伍之间没有差距
顺序排名	所有记录号都存在，因此所有等级都是唯一的
分数排名	等级总和与顺序等级相同

Further: 

进一步：

For the ordinal and the fractional strategies, the sum of the ranks is determined by the count of the values only, not the values.

对于顺序策略和分数策略，等级的总和仅由值的计数确定，而不由值的计数确定。

Finally, in a database, the most difficult strategy to implement is, by far, the ordinal strategy. This is because there is no way to distinguish records with identical field values from each other. As mentioned in the explanation (see link above), a random ranking of such values should not be used, because the ranking should be firm, meaning that repeated calls for the ranks should return identical rankings. Thus, some additional method should be applied.

最后，在数据库中，最难实施的策略是到目前为止的顺序策略 。 这是因为无法将具有相同字段值的记录区分开。 如解释中所述（请参见上面的链接），不应使用此类值的随机排名，因为排名应该是坚定的，这意味着重复调用排名应该返回相同的排名。 因此，应采用一些其他方法。

One method, that will provide predictable results, is to sort on a second field. For a motorrace, for example, this second value could be the driver's grid position, name, or birthdate.

一种将提供可预测结果的方法是在第二个字段上进行排序。 例如，对于赛车，该第二个值可以是驾驶员的网格位置 ，姓名或生日。

This option has been implemented and will be discussed later.

此选项已实现，将在后面讨论。

使用纯SQL计算等级 (Calculate rank with pure SQL)

As an example, we can take the Products table of the Northwind example database and rank the products by their cost/price. These contain many duplicates, thus are well suited to illustrate the differences between the ranking strategies.

例如，我们可以使用罗斯文（Northwind）示例数据库的“ 产品”表，并按其成本/价格对产品进行排名。 这些包含许多重复项，因此非常适合说明排名策略之间的差异。

The SQL of the query will look like this:

查询SQL将如下所示：

SELECT 
Products.*, 

1+(Select Count(*) 
From Products As T 
Where T.[Standard Cost] > Products.[Standard Cost]) AS Competition, 

(Select Count(*) 
From Products As T 
Where T.[Standard Cost] >= Products.[Standard Cost]) AS ModComp, 

1+(Select Count(*) 
From (Select Distinct S.[Standard Cost] From Products As S) As T 
Where T.[Standard Cost] > Products.[Standard Cost]) AS Dense, 

(Select Count(*) 
From Products As T 
Where T.[Standard Cost] >= Products.[Standard Cost])
-(Select Count(*) From Products As S 
Where S.[Standard Cost] = Products.[Standard Cost] And S.[Product Code] < Products.[Product Code]) AS Ordinal, 

([Competition]+([Competition]
+(Select Count(*) 
From Products As T 
Where T.[Standard Cost] = Products.[Standard Cost])-1))/2 AS Fractional

FROM 
Products
ORDER BY 
Products.[Standard Cost] DESC; 

The five strategies have been separated by an empty line, so it should be relatively easy to follow the code, which counts the records and - for each strategy - adds the little twist that makes it unique.

五个策略之间用空行分隔，因此遵循代码（记录计数）的代码应该相对容易，并且-对于每个策略-都添加了使它独特的小改动。

Notice, that for the ordinal strategy - the strategy that needs a second value to sort on - the Product Code has been chosen because then you in plain English can explain why the ranking is like it is. That would not be possible if the ID has been used, as this - even being unique - could appear as more or less random. It could even be hidden from the view as its value - contrary to the Product Code - has no meaning.

注意，对于顺序策略 （需要第二个值进行排序的策略），已选择了产品代码，因为这样您便可以用简单的英语来解释排名为何如此 。 如果使用了ID，那将是不可能的，因为即使是唯一的ID也可能或多或少地随机出现。 它甚至可能从视图中隐藏，因为它的值（与产品代码相反）没有任何意义。

The output will be as shown here (click the picture for a better view):

输出将如下所示（单击图片可获得更好的视图）：

You will notice, that the first differences occur for the price of $10.50, and then these will increase as we move down through the records.

您会注意到，第一个差异是价格为$ 10.50，然后随着我们向下浏览记录而增加。

用VBA计算排名 (Calculate rank with VBA)

While SQL can be convenient for small recordsets, it may be slow for listing many records because of the subqueries.

虽然SQL对于小型记录集可能很方便，但由于子查询的原因，列出许多记录可能会很慢。

Thus, I have created a function, RowRank, using a collection in VBA that will calculate the rank of the records for all five strategies with one table scan only.

因此，我使用VBA中的一个集合创建了一个RowRank函数，该函数将仅使用一个表扫描来计算所有五种策略的记录等级。

The trick is to collect the values in a static collection holding each record's value and an array with the calculated rank for each strategy. Then, for any value and any of the five strategies, the rank can be looked up in a split second.

诀窍是在包含每个记录的值的静态集合中收集值，并为每个策略计算一个具有排名的数组。 然后，对于任何值和五种策略中的任何一种，都可以在瞬间查看排名。

It takes a little code but, when ready, the advantages are that it is very simple to implement in a form or query, and that it runs truly fast.

它只需要少量代码，但是准备就绪后的好处是，以表单或查询的形式实现非常简单，并且运行速度非常快。

It is a single function supported by two enums:

它是一个受两个枚举支持的函数：

' Returns, by the value of a field, the rank of one or more records of a table or query.
' Supports all five common ranking strategies (methods).
'
' Source:
'   WikiPedia: https://en.wikipedia.org/wiki/Ranking
'
' Supports ranking of descending as well as ascending values.
' Any ranking will require one table scan only.
' For strategy Ordinal, a a second field with a subvalue must be used.
'
' Typical usage (table Products of Northwind sample database):
'
'   SELECT Products.*, RowRank("[Standard Cost]","[Products]",[Standard Cost]) AS Rank
'   FROM Products
'   ORDER BY Products.[Standard Cost] DESC;
'
' Typical usage for strategy Ordinal with a second field ([Product Code]) holding the subvalues:
'
'   SELECT Products.*, RowRank("[Standard Cost],[Product Code]","[Products]",[Standard Cost],[Product Code],2) AS Ordinal
'   FROM Products
'   ORDER BY Products.[Standard Cost] DESC;
'
' To obtain a rank, the first three parameters must be passed.
' Four parameters is required for strategy Ordinal to be returned properly.
' The remaining parameters are optional.
'
' The ranking will be cached until Order is changed or RowRank is called to clear the cache.
' To clear the cache, call RowRank with no parameters:
'
'   RowRank
'
' Parameters:
'
'   Expression: One field name for other strategies than Ordinal, two field names for this.
'   Domain:     Table or query name.
'   Value:      The values to rank.
'   SubValue:   The subvalues to rank when using strategy Ordinal.
'   Strategy:   Strategy for the ranking.
'   Order:      The order by which to rank the values (and subvalues).
'
' 2019-07-11. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RowRank( _
Optional ByVal Expression As String, _
Optional ByVal Domain As String, _
Optional ByVal Value As Variant, _
Optional ByVal SubValue As Variant, _
Optional ByVal Strategy As ApRankingStrategy = ApRankingStrategy.apStandardCompetition, _
Optional ByVal Order As ApRankingOrder = ApRankingOrder.apDescending) _
As Double

Const SqlMask1          As String = "Select Top 1 {0} From {1}"
Const SqlMask           As String = "Select {0} From {1} Order By 1 {2}"
Const SqlOrder          As String = ",{0} {1}"
Const OrderAsc          As String = "Asc"
Const OrderDesc         As String = "Desc"
Const FirstStrategy     As Integer = ApRankingStrategy.apDense
Const LastStrategy      As Integer = ApRankingStrategy.apFractional

' Expected error codes to accept.
Const CannotAddKey      As Long = 457
Const CannotFindKey     As Long = 5
' Uncommon character string to assemble Key and SubKey as a compound key.
Const KeySeparator      As String = "¤§¤"

' Array of the collections for the five strategies.
Static Ranks(FirstStrategy To LastStrategy) As Collection
' The last sort order used.
Static LastOrder        As ApRankingOrder

Dim Records             As DAO.Recordset

' Array to hold the rank for each strategy.
Dim Rank(FirstStrategy To LastStrategy)     As Double

Dim Item                As Integer
Dim Sql                 As String
Dim SortCount           As Integer
Dim SortOrder           As String
Dim LastKey             As String
Dim Key                 As String
Dim SubKey              As String
Dim Dupes               As Integer
Dim Delta               As Long
Dim ThisStrategy        As ApRankingStrategy

On Error GoTo Err_RowRank

If Expression = "" Then
' Erase the collections of keys.
For Item = LBound(Ranks) To UBound(Ranks)
Set Ranks(Item) = Nothing
Next
Else
If LastOrder <> Order Or Ranks(FirstStrategy) Is Nothing Then
' Initialize the collections and reset their ranks.
For Item = LBound(Ranks) To UBound(Ranks)
Set Ranks(Item) = New Collection
Rank(Item) = 0
Next

' Build order clause.
Sql = Replace(Replace(SqlMask1, "{0}", Expression), "{1}", Domain)
SortCount = CurrentDb.OpenRecordset(Sql, dbReadOnly).Fields.Count

If Order = ApRankingOrder.apDescending Then
' Descending sorting (default).
SortOrder = OrderDesc
Else
' Ascending sorting.
SortOrder = OrderAsc
End If
LastOrder = Order

' Build SQL.
Sql = Replace(Replace(Replace(SqlMask, "{0}", Expression), "{1}", Domain), "{2}", SortOrder)
' Add a second sort field, if present.
If SortCount >= 2 Then
Sql = Sql & Replace(Replace(SqlOrder, "{0}", 2), "{1}", SortOrder)
End If

' Open ordered recordset.
Set Records = CurrentDb.OpenRecordset(Sql, dbReadOnly)
' Loop the recordset once while creating all the collections of ranks.
While Not Records.EOF
Key = CStr(Nz(Records.Fields(0).Value))
SubKey = ""
' Create the sub key if a second field is present.
If SortCount > 1 Then
SubKey = CStr(Nz(Records.Fields(1).Value))
End If

If LastKey <> Key Then
' Add new entries.
For ThisStrategy = FirstStrategy To LastStrategy
Select Case ThisStrategy
Case ApRankingStrategy.apDense
Rank(ThisStrategy) = Rank(ThisStrategy) + 1
Case ApRankingStrategy.apStandardCompetition
Rank(ThisStrategy) = Rank(ThisStrategy) + 1 + Dupes
Dupes = 0
Case ApRankingStrategy.apModifiedCompetition
Rank(ThisStrategy) = Rank(ThisStrategy) + 1
Case ApRankingStrategy.apOrdinal
Rank(ThisStrategy) = Rank(ThisStrategy) + 1
' Add entry using both Key and SubKey
Ranks(ThisStrategy).Add Rank(ThisStrategy), Key & KeySeparator & SubKey
Case ApRankingStrategy.apFractional
Rank(ThisStrategy) = Rank(ThisStrategy) + 1 + Delta / 2
Delta = 0
End Select
If ThisStrategy = ApRankingStrategy.apOrdinal Then
' Key with SubKey has been added above for this strategy.
Else
' Add key for all other strategies.
Ranks(ThisStrategy).Add Rank(ThisStrategy), Key
End If
Next
LastKey = Key
Else
' Modify entries and/or counters for those strategies that require this for a repeated key.
For ThisStrategy = FirstStrategy To LastStrategy
Select Case ThisStrategy
Case ApRankingStrategy.apDense
Case ApRankingStrategy.apStandardCompetition
Dupes = Dupes + 1
Case ApRankingStrategy.apModifiedCompetition
Rank(ThisStrategy) = Rank(ThisStrategy) + 1
Ranks(ThisStrategy).Remove Key
Ranks(ThisStrategy).Add Rank(ThisStrategy), Key
Case ApRankingStrategy.apOrdinal
Rank(ThisStrategy) = Rank(ThisStrategy) + 1
' Will fail for a repeated value of SubKey.
Ranks(ThisStrategy).Add Rank(ThisStrategy), Key & KeySeparator & SubKey
Case ApRankingStrategy.apFractional
Rank(ThisStrategy) = Rank(ThisStrategy) + 0.5
Ranks(ThisStrategy).Remove Key
Ranks(ThisStrategy).Add Rank(ThisStrategy), Key
Delta = Delta + 1
End Select
Next
End If
Records.MoveNext
Wend
Records.Close
End If

' Retrieve the rank for the current strategy.
If Strategy = ApRankingStrategy.apOrdinal Then
' Use both Value and SubValue.
Key = CStr(Nz(Value)) & KeySeparator & CStr(Nz(SubValue))
Else
' Use Value only.
Key = CStr(Nz(Value))
End If
' Will fail if key isn't present.
Rank(Strategy) = Ranks(Strategy).Item(Key)
End If

RowRank = Rank(Strategy)

Exit_RowRank:
Exit Function

Err_RowRank:
Select Case Err
Case CannotAddKey
' Key is present, thus cannot be added again.
Resume Next
Case CannotFindKey
' Key is not present, thus cannot be removed.
Resume Next
Case Else
' Some other error. Ignore.
Resume Exit_RowRank
End Select

End Function 

Please note the in-line comments that explain all the steps taken.

请注意解释所有步骤的在线注释。

These are the enums to complete the picture:

这些是完成图片的枚举：

'   Ranking strategies. Numeric values match those of:
'   https://se.mathworks.com/matlabcentral/fileexchange/70301-ranknum
Public Enum ApRankingStrategy
apDense = 1
apOrdinal = 2
apStandardCompetition = 3
apModifiedCompetition = 4
apFractional = 5
End Enum

'   Ranking orders.
Public Enum ApRankingOrder
apDescending = 0
apAscending = 1
End Enum 

Now, using this function, we can build a query returning exactly the same output as the query above:

现在，使用此函数，我们可以构建一个查询，返回与上述查询完全相同的输出：

SELECT 
Products.*, 
RowRank("[Standard Cost],[Product Code]","Products",[Standard Cost],[Product Code],3) AS Competition, 
RowRank("[Standard Cost],[Product Code]","Products",[Standard Cost],[Product Code],4) AS ModComp, 
RowRank("[Standard Cost],[Product Code]","Products",[Standard Cost],[Product Code],1) AS Dense, 
RowRank("[Standard Cost],[Product Code]","Products",[Standard Cost],[Product Code],2) AS Ordinal, 
RowRank("[Standard Cost],[Product Code]","Products",[Standard Cost],[Product Code],5) AS Fractional
FROM 
Products
ORDER BY 
Products.[Standard Cost] DESC; 

You'll notice how much simpler it is. 

您会注意到它要简单得多。

Also note, that the secondary value - the Product Code - has been included, as we wish to also list the ordinal rank. From the in-line comments (at the top of the code block) you'll see, that the simplest default implementation is:

另请注意，由于我们也希望列出序数排名，因此已包含次要值-产品代码-。 从内联注释（在代码块顶部），您将看到最简单的默认实现是：

SELECT Products.*, RowRank("[Standard Cost]","[Products]",[Standard Cost]) AS Rank
FROM Products
ORDER BY Products.[Standard Cost] DESC; 

Just for verification, the output of the extended query is:

仅出于验证目的，扩展查询的输出为：

Cached ranking

缓存排名

As the ranks are cached - they are stored in the collection - you may wish to reset the ranking (clear the cache). This can be done in two ways:

由于排名被缓存-它们被存储在集合中-您可能希望重置排名 （清除缓存）。 这可以通过两种方式完成：

• call RowRank with another sort order, or:
  用另一个排序顺序调用RowRank，或者：
• call RowRank with no arguments at all:
  完全不带参数调用RowRank：

RowRank 

排序表格记录 (Ranking records of a form)

For many purposes, you may wish to calculate and display the ranks directly in a form without having to build a special query. You may even wish to select between the five strategies on the fly.

出于许多目的，您可能希望直接以表格形式计算和显示排名，而无需建立特殊查询。 您甚至可能希望在五种策略之间进行选择。

This is easily done using the function.  Just add a textbox with this expression as ControlSource:

使用此功能很容易做到。 只需添加带有此表达式的文本框作为ControlSource：

=RowRank("[Standard Cost],[Product Code]","Products",[Standard Cost],[Product Code],[Strategy],[RankOrder]) 

Add two combo-boxes (here named Strategy and RankOrder) to select the strategy and sort order, and the result may appear like:

添加两个组合框（此处分别称为Strategy和RankOrder ）以选择策略和排序顺序，结果可能如下所示：

Change the strategy or the sort order, and the form will requery the ranks immediately.

更改策略或排序顺序，表格将立即重新查询排名。

缓存排名 (Cached ranking)

Too make sure that the cache is cleared before opening the form, you may include this single line of code in the Load event of the form:

太确保打开表单之前已清除缓存，您可能在表单的Load事件中包含以下单行代码：

Private Sub Form_Load()

' Reset all ranks.
RowRank

End Sub 

As a clean-up, it can be included in the UnLoad event as well.

作为清理，它也可以包含在UnLoad事件中。

按表格排序 (Sorting on rank in a form)

Most often you will have sorted the form on the field to rank. But if you can't do that and wish to sort on the rank itself, it must be calculated in the source query of the form. This means, that the query has a field holding the rank, and that a textbox on the form is bound to this.

通常，您会在字段上对表格进行排序以进行排名。 但是，如果您不能这样做，并且希望对排名本身进行排序，则必须在表单的源查询中对其进行计算。 这意味着查询具有一个保留等级的字段，并且表单上的文本框已绑定到该字段。

However, if you sort on the field to rank, the textbox holding the rank will automatically be sorted. This opens for alternative methods for calculating the rank.

但是，如果您在要排序的字段上进行排序，则具有该排名的文本框将自动进行排序。 这为计算等级的替代方法打开了大门。

First, as shown above, the RowRank function can be used directly as the ControlSource, for example:

首先 ，如上所示，RowRank函数可以直接用作ControlSource，例如：

=RowRank("[Standard Cost]","Products",[Standard Cost],Null,5) 

Second, and mostly for completeness, you can use the domain function DCount, though it often will be too slow:

其次 ，主要是出于完整性考虑，您可以使用域函数DCount ，尽管它通常会太慢：

=1+DCount("*","[Products]","[Standard Cost]>" & Str([Standard Cost]) & "") 

Here, the purpose of Str is to force a proper format of a decimal value with a dot as the decimal separator.

在这里， Str的目的是强制使用点作为小数点分隔符的十进制值的正确格式。

演示版 (Demo)

The attached demo application (Access 2016) contains, of course, the code as listed as well as four forms  covering everything discussed above. 

随附的演示应用程序（Access 2016）当然包含列出的代码以及涵盖上述所有内容的四种形式 。

Also, the two queries - using SQL and VBA respectively - are included for you to check out:

此外，还包括两个查询（分别使用SQL和VBA）供您检出：

RowNumbers 1.4.1.zip

行号1.4.1.zip

Current code is also on GitHub: VBA.RowNumbers

当前代码也在GitHub上 ： VBA.RowNumbers

进一步阅读 (Further reading)

You may also enjoy my previous articles covering all other aspects of row/record numbering:

您可能还喜欢我以前的文章，涵盖行/记录编号的所有其他方面：

Random Rows in Microsoft Access

Microsoft Access中的随机行

Sequential Rows in Microsoft Access

Microsoft Access中的顺序行

I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.

希望本文对您有所帮助。 鼓励您在下面提出问题，报告任何错误或对此作出任何其他评论。

Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.

注意 ：如果您需要有关此主题的更多“支持”，请考虑使用Experts Exchange 的“提问”功能。 我会监督提出的问题，并很高兴与其他电子工程师一起为以这种方式提出的问题提供所需的任何其他支持。

Please do not forget to press the "Thumbs Up" button if you think this article was helpful and valuable for EE members.

如果您认为本文对EE成员有用且有价值，请不要忘记按下“竖起大拇指”按钮。

翻译自: https://www.experts-exchange.com/articles/33651/Ranking-rows-in-Microsoft-Access.html

access排名