access排名_在MicrosoftAccess中对⾏进⾏排名
排名
This is the third article on row numbers in Microsoft Access.
这是有关Microsoft Access中⾏号的第三篇⽂章。
The first is about .
⾸先是关于 。
The cond is about .
第⼆个关于 。
排名如何？ (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, asce
nding is also common and is what is ud 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.67.3
Ascending2314
Descending3241
值： 3.4 5.1 2.6 7.3
上升 2 3 1个 4
降序 3 2 4 1个
排名策略 (Ranking strategies)
A list of the strategies with a detailed explanation for each can be found on WikiPedia: .
这些策略的列表以及每种策略的详细说明可以在WikiPedia ： 找到。
Therefore, here we will only quote the list of strategies and the two matching formulas of Excel in ca you are familiar with the:
因此，在这⾥我们仅引⽤策略列表和两个匹配的Excel公式，以防您熟悉以下内容：
Name nick name excel equivalent formula
Standard competition ranking"1224" ranking RANK.EQ
Modified competition ranking"1334" ranking
Den 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 u? For some scenarios, a very strict strategy is either common or has been decided for you. For other scenarios - indeed tho of your own - you must choo one. Here it can be of value to be acquainted with the main features for each of the, which are:
那么，您应该使⽤什么策略？ 在某些情况下，⾮常严格的策略要么很常见，要么已为您决定。 对于其他情况-实际上是您⾃⼰的情况-您必须选择⼀种。 在这⾥熟悉以下每个⽅⾯的主要功能可能很有价值：
strategy main feature
Standard competition ranking Identical values are ranked the same
Modified competition ranking Identical values are ranked the same
Den ranking No gaps between the ranks
strategy main feature
Ordinal ranking All record numbers are prent, 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 databa, the most difficult strategy to implement is, by far, the ordinal strategy. This is becau there is no way to distinguish records with identical field values from each other. As mentioned in the explanation (e link above), a
random ranking of such values should not be ud, becau 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 cond field. For a motorrace, for example, this cond value could be the driver's , name, or birthdate.
⼀种将提供可预测结果的⽅法是在第⼆个字段上进⾏排序。 例如，对于赛车，该第⼆个值可以是驾驶员的 ，姓名或⽣⽇。
This option has been implemented and will be discusd later.
此选项已实现，将在后⾯讨论。
使⽤纯SQL计算等级 (Calculate rank with pure SQL)
弟子规泛爱众
As an example, we can take the Products table of the Northwind example databa and rank the products by their
cost/price. The contain many duplicates, thus are well suited to illustrate the differences between t
he ranking strategies.例如，我们可以使⽤罗斯⽂（Northwind）⽰例数据库的“ 产品”表，并按其成本/价格对产品进⾏排名。 这些包含许多重复项，因此⾮常适合说明排名策略之间的差异。
The SQL of the query will look like this:
查询SQL将如下所⽰：
1SELECT
2Products.*,
3
41+(Select Count(*)
5From Products As T
6Where T.[Standard Cost] > Products.[Standard Cost]) AS Competition,
7
8(Select Count(*)
9From Products As T
宝贝用英语怎么说10Where T.[Standard Cost] >= Products.[Standard Cost]) AS ModComp,
11
121+(Select Count(*)
13From (Select Distinct S.[Standard Cost] From Products As S) As T
14Where T.[Standard Cost] > Products.[Standard Cost]) AS Den,
15
16(Select Count(*)
17From Products As T
18Where T.[Standard Cost] >= Products.[Standard Cost])
19-(Select Count(*) From Products As S
20Where S.[Standard Cost] = Products.[Standard Cost] And S.[Product Code] < Products.[Product Code]) AS Ordinal,
21
22([Competition]+([Competition]
23+(Select Count(*)
24From Products As T
25Where T.[Standard Cost] = Products.[Standard Cost])-1))/2 AS Fractional
26
27FROM
28Products
29ORDER BY
30Products.[Standard Cost] DESC;
The five strategies have been parated 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 cond value to sort on - the Product Code has been chon becau then you in plain English can explain why the ranking is like it is. That would not be possible if the ID has been ud, 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 the will increa as we move down through the records.
您会注意到，第⼀个差异是价格为$ 10.50，然后随着我们向下浏览记录⽽增加。
⽤VBA计算排名 (Calculate rank with VBA)
While SQL can be convenient for small recordts, it may be slow for listing many records becau 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 cond.
诀窍是在包含每个记录的值的静态集合中收集值，并为每个策略计算⼀个具有排名的数组。 然后，对于任何值和五种策略中的任何⼀种，都可以在瞬间查看排名。
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:
它是⼀个受两个枚举⽀持的函数：
1' Returns, by the value of a field, the rank of one or more records of a table or query.
2' Supports all five common ranking strategies (methods).
3'
4' Source:
稚嫩的青春
5' WikiPedia: en.wikipedia/wiki/Ranking
6'
7' Supports ranking of descending as well as ascending values.
8' Any ranking will require one table scan only.
9' For strategy Ordinal, a a cond field with a subvalue must be ud.
10'
11' Typical usage (table Products of Northwind sample databa):
12'
13' SELECT Products.*, RowRank("[Standard Cost]","[Products]",[Standard Cost]) AS Rank
14' FROM Products
15' ORDER BY Products.[Standard Cost] DESC;
16'
17' Typical usage for strategy Ordinal with a cond field ([Product Code]) holding the subvalues:
18'
19' SELECT Products.*, RowRank("[Standard Cost],[Product Code]","[Products]",[Standard Cost],[Product Code],2) AS Ordinal
20' FROM Products
21' ORDER BY Products.[Standard Cost] DESC;
22'
23' To obtain a rank, the first three parameters must be pasd.
石竹花语
24' Four parameters is required for strategy Ordinal to be returned properly.
25' The remaining parameters are optional.
26'
27' The ranking will be cached until Order is changed or RowRank is called to clear the cache.
28' To clear the cache, call RowRank with no parameters:
29'
30' RowRank
31'
32' Parameters:
33'股票行情走势图
34' Expression: One field name for other strategies than Ordinal, two field names for this.
35' Domain: Table or query name.
36' Value: The values to rank.
37' SubValue: The subvalues to rank when using strategy Ordinal.
38' Strategy: Strategy for the ranking.
39' Order: The order by which to rank the values (and subvalues).
落水记40'
41' 2019-07-11. Gustav Brock, Cactus Data ApS, CPH.
42'
43Public Function RowRank( _
44Optional ByVal Expression As String, _
45Optional ByVal Domain As String, _
