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查询实现方式
相比于VBA代码方式,在成绩值发生修改时,SQL查询可以自动更新排名数据,而不需要像VBA过程要每次都手动重新运算.
1 SELECT 2 Score.id, 3 Score.Score, 4 Score.Rank1_VBA, 5 Score.Rank2_VBA, 6 (Select Count(*)+1 From score AS Score_1 Where Score_1.Score>Score.Score) AS Rank1, 7 (Select Count(*)+1 From (Select Distinct Score_1.score From score AS Score_1) As tbl Where tbl.Score>Score.Score) AS Rank2 8 FROM Score;
创建这个查询后,会活的如下所示的运行结果:Rank1与Rank1_VBA对应,Rank2与Rank2_VBA对应.
