Access无rank()等窗口函数,但可以通过sql语句的嵌套实现对数据的连续和非连续排名。
数据表:成绩表
一、直接用sql查询
非连续排名:
(计算表中比当前分数更高的记录的条数,再+1,即为非连续排名)
SELECT 成绩表.*,
(select count(1)+1 from 成绩表 as 成绩表2 where 成绩表2.分数>成绩表.分数) as 非连续排名
from 成绩表
连续排名:
(计算表中比当前分数更高的不重复记录的条数,再+1,即为连续排名)
SELECT 成绩表.*,
(select count(1)+1 from (select distinct 分数 from 成绩表 as 成绩表2) where 成绩表2.分数>成绩表.分数) as 连续排名
from 成绩表
二、调用sub子程序
创建模块,在模块中定义子程序RankField
(逻辑与上文sql一致,通过子程序进行封装,可以在宏或立即窗口中调用)
代码:
#参数说明
#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
Sub 过程可传递参数但无返回值,定义时无需定义返回值类型,有两种调用语法。
1.Call RankField(参数1,参数2,参数3,参数4)
2.RankField 参数1,参数2,参数3,参数4
快捷键Ctrl+G,打开立即窗口,输入语句后,回车,查看成绩表可以看到排名已经更新。