office交流网--QQ交流群号

Access培训群:792054000         Excel免费交流群群:686050929          Outlook交流群:221378704    

Word交流群:218156588             PPT交流群:324131555

Access SQL和代码实现连续及不连续Rank排名

2019-12-22 08:00:00
Alexywt
转贴
970

在Excel中我们可以使用Rank函数对数据进行排名操作。

而Access是没有Rank函数的,所以不能直接用此函数排名操作。

下面介绍使用VBA代码和建立SQL查询来完成排序操作。


一、排名的种类跟算法


1、非连续排名

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

2、连续排名

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



二、不同的实现方式


如下图所示分别为表的结构及部分初始数据:


1、VBA实现方式

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

TableRanked:需排名的表名

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

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

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

Sub RankField(TableRanked As String, FieldRanked As String, FieldResult As String, NormalRank As Boolean)
    Dim rs As New ADODB.Recordset
    Dim rs1 As New ADODB.Recordset
    rs.Open "Select " & FieldRanked & "," & FieldResult & " From " & TableRanked, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    Do Until rs.EOF
        If NormalRank Then
            rs1.Open "Select Count(*)+1 as CountNum From " & TableRanked & " Where " & FieldRanked & ">" & rs.Fields(FieldRanked).Value, _
                CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
        Else
            rs1.Open "Select Count(*) as CountNum From (Select Distinct " & FieldRanked & " From " & TableRanked & " Where " & FieldRanked & ">=" & rs.Fields(FieldRanked).Value & ")", _
                CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
        End If
        rs.Fields(FieldResult).Value = rs1!CountNum.Value
        rs1.Close
        rs.MoveNext
    Loop
    rs.Close
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过程要每次都手动重新运算.

SELECT 
    Score.id, 
    Score.Score, 
    Score.Rank1_VBA, 
    Score.Rank2_VBA, 
    (Select Count(*)+1 From score AS Score_1 Where Score_1.Score>Score.Score) AS Rank1, 
    (Select Count(*)+1 From (Select Distinct Score_1.score From score AS Score_1) As tbl Where tbl.Score>Score.Score) AS Rank2
FROM Score;

创建这个查询后,会活的如下所示的运行结果:Rank1与Rank1_VBA对应,Rank2与Rank2_VBA对应.

    分享