Access SQL和代碼實現連續(xù)及不連續(xù)Rank排名

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

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

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;