Access SQL实现连续及不连续Rank排名

一、关于起因

在Excel中我们经常使用Rank函数对数据进行排名操作。而在Access中我们要进行排名是找不到这个Rank函数的,此时我们需要自己书写VBA代码或者建立SQL查询来完成排序操作。

今天我就来讲讲如何在Access中进行Rank排名操作,首先我给大家分析一下排名的种类跟实现方法。

二、排名的种类跟算法

1、非连续排名

逻辑算法:对于一组数列里的某个数字而言,其非连续排名是指:在该组数列里比该数字大的所有数字的个数+1

2、连续排名

逻辑算法:对于一组数列里的某个数字而言,其连续排名是指:在该组数列里比该数字大的所有非重复数字的个数+1

三、不同的实现方式

首先我们做好准备工作,我们要建立必要表及其内部数据,如下图所示分别为表的结构及部分初始数据:

1、VBA实现方式

我写了一个Sub过程RankField,该过程的参数说明如下:

TableRanked:需排名的表名

FieldRanked:数据所在字段的字段名

FieldResult:排名后结果存储的字段名

NormalRank:是否是常规排名(True是常规排名,非连续排名,Excel中的Rank函数即为非连续排名;False为连续排名)

 1 Sub RankField(TableRanked As String, FieldRanked As String, FieldResult As String, NormalRank As Boolean)
 2     Dim rs As New ADODB.Recordset
 3     Dim rs1 As New ADODB.Recordset
 4     rs.Open "Select " & FieldRanked & "," & FieldResult & " From " & TableRanked, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
 5     Do Until rs.EOF
 6         If NormalRank Then
 7             rs1.Open "Select Count(*)+1 as CountNum From " & TableRanked & " Where " & FieldRanked & ">" & rs.Fields(FieldRanked).Value, _
 8                 CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
 9         Else
10             rs1.Open "Select Count(*) as CountNum From (Select Distinct " & FieldRanked & " From " & TableRanked & " Where " & FieldRanked & ">=" & rs.Fields(FieldRanked).Value & ")", _
11                 CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
12         End If
13         rs.Fields(FieldResult).Value = rs1!CountNum.Value
14         rs1.Close
15         rs.MoveNext
16     Loop
17     rs.Close
18 End Sub

按下Ctrl+G,切换到立即窗口,分别输入如下类似的代码:

1 RankField "Score","Score","Rank1_VBA",true
2 RankField "Score","Score","Rank2_VBA",False

我们会得到如下类似的结果,Rank1_VBA列为非连续排名结果,Rank2_VBA为连续排名结果:

2、SQL查询实现方式

 

未完待续......

相关内容推荐