谢谢!
在第一次调用Rnd之前,尝试调用一次Randomize。正如Rnd的帮助主题所说,“在调用Rnd之前,使用不带参数的Randomize语句,以基于系统计时器的种子初始化随机数生成器。”
Randomize
acbGetRandom = Rnd
End Function然后我将该函数添加到我的查询中,使用:RandomID: acbGetRandom([ID]) 在字段中。希望这能帮助到某些人! - Ben“Rnd”不好吗?
Option Compare Database
Option Explicit
Sub Test()
Randomize
Dim x As Integer
'Print the first field of a 100 random records
For x = 0 To 100
CallRandomRecord
Next x
End Sub
Sub CallRandomRecord()
Dim rs As DAO.Recordset
Dim recordCount As Long
Dim randomRecord As Long
Set rs = CurrentDb.OpenRecordset("SELECT * FROM MyTable")
rs.MoveLast 'To get the count
rs.MoveFirst
recordCount = rs.recordCount - 1
randomRecord = CLng((recordCount) * Rnd)
rs.Move randomRecord
Debug.Print "Random Record No:" & randomRecord & " Field 1: " & rs.Fields(0)
End Sub
TimeThem 模块顶部的三个常量即可:
Private Declare Function GetTickCount Lib "kernel32" () As Long
Sub TimeThem()
Const Loops As Integer = 10
Const TblName As String = "Batches"
Const FldName As String = "BatchName"
Const IndexFld As String = "BatchID"
Dim i As Integer, s As Long, dummy As Variant
s = GetTickCount
For i = 1 To Loops
dummy = HarkinsRandom(TblName, FldName)
Next i
Debug.Print "Harkins:"; GetTickCount - s
s = GetTickCount
For i = 1 To Loops
dummy = RandomRecord(TblName, FldName)
Next i
Debug.Print "RandomRecord:"; GetTickCount - s
s = GetTickCount
For i = 1 To Loops
dummy = RandomRecordWithIndex(TblName, FldName, IndexFld)
Next i
Debug.Print "WithIndex:"; GetTickCount - s
s = GetTickCount
For i = 1 To Loops
dummy = CallRandomRecord(TblName, FldName)
Next i
Debug.Print "CallRandom:"; GetTickCount - s
End Sub
Function HarkinsRandom(TblName As String, FldName As String)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(" SELECT TOP 1 " & FldName & _
" FROM " & TblName & _
" ORDER BY GetRandomValue(" & FldName & ")", _
dbOpenForwardOnly)
HarkinsRandom = rs(0)
End Function
Public Function GetRandomValue(fld As Variant)
Randomize
GetRandomValue = Rnd(1)
End Function
Function RandomRecord(TblName As String, FldName As String)
Dim NumRecs As Long, RecNum As Long
Dim SQL As String, SubSQL As String, rs As DAO.Recordset
Dim IndexFld As String
Randomize
NumRecs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & TblName, dbOpenForwardOnly)(0)
RecNum = Int(Rnd() * NumRecs + 1)
SQL = " SELECT TOP 1 " & FldName & _
" FROM (" & _
" SELECT TOP " & RecNum & " " & FldName & " " & _
" FROM " & TblName & _
" ORDER BY " & FldName & ")" & _
" ORDER BY " & FldName & " DESC"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly)
RandomRecord = rs(0)
End Function
Function RandomRecordWithIndex(TblName As String, FldName As String, _
Optional IndexedFieldName As String)
Dim NumRecs As Long, RecNum As Long
Dim SQL As String, SubSQL As String, rs As DAO.Recordset
Dim IndexFld As String
Randomize
NumRecs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & TblName, dbOpenForwardOnly)(0)
RecNum = Int(Rnd() * NumRecs + 1)
If Len(IndexedFieldName) = 0 Or IndexedFieldName = FldName Then
SQL = " SELECT TOP 1 " & FldName & _
" FROM (" & _
" SELECT TOP " & RecNum & " " & FldName & " " & _
" FROM " & TblName & _
" ORDER BY " & FldName & ")" & _
" ORDER BY " & FldName & " DESC"
Else
SQL = " SELECT TOP 1 " & FldName & _
" FROM (" & _
" SELECT TOP " & RecNum & " " & FldName & ", " & IndexedFieldName & _
" FROM " & TblName & _
" ORDER BY " & IndexedFieldName & ")" & _
" ORDER BY " & IndexedFieldName & " DESC"
End If
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly)
RandomRecordWithIndex = rs(0)
End Function
Function CallRandomRecord(TblName As String, FldName As String)
Dim rs As DAO.Recordset
Dim recordCount As Long
Dim RandomRecord As Long
Set rs = CurrentDb.OpenRecordset("SELECT " & FldName & " FROM " & TblName)
rs.MoveLast 'To get the count
rs.MoveFirst
recordCount = rs.recordCount - 1
RandomRecord = CLng((recordCount) * Rnd)
rs.Move RandomRecord
CallRandomRecord = rs(0)
' Debug.Print "Random Record No:" & randomRecord & " Field 1: " & rs.Fields(0)
End Function
以下是针对一张约有50,000条记录的表格进行测试的结果(该表格是本地连接的Jet表格,即在我运行测试的同一台计算机上的.mdb文件中):
Harkins: 4461
RandomRecord: 2528
WithIndex: 1918
CallRandom: 172
Harkins: 4150
RandomRecord: 2278
WithIndex: 2043
CallRandom: 47
CallRandom: 63
WithIndex: 2090
RandomRecord: 2324
Harkins: 4197
CallRandom: 46
WithIndex: 1997
RandomRecord: 2169
Harkins: 4150
我运行了四次,第一次后颠倒顺序以考虑潜在的缓存优势。如您所见,我的两个函数的运行速度大约是Harkins解决方案的两倍,但@ray023的解决方案最慢时快了25倍(最快时快了近100倍)。
但无论如何,请根据自己的数据进行基准测试。
请参考Susan Harkins在TechRepublic上的这篇文章:http://www.techrepublic.com/blog/howdoi/how-do-i-retrieve-a-random-set-of-records-in-microsoft-access/149
我在这个查询中使用了她的GetRandomValue函数,它每次返回不同的记录。
SELECT TOP 1 f.id, GetRandomValue(f.id) AS rnd_value
FROM tblFoo AS f
ORDER BY 2;
这个函数:
Public Function GetRandomValue(fld As Variant)
Randomize
GetRandomValue = Rnd(1)
End Function
注意:这种方法需要对表中的每一行运行一个函数。对于小到中等大小的表可能还能容忍,但是不应该在非常大的表上使用。
Round(100 * Rnd(), 0)
...然后使用它来检索图像。如果图像表具有自动编号的 PK,您可以直接使用它,这将非常快速。如果您的图像位于子窗体中,您可以将 LinkMaster 设置为字面 PK 值,这将为您检索图像。
关于 Randomize(),我似乎无法在即时窗口中调用 Rnd() 时重复它,所以我不确定是否需要它。
但对我来说,这似乎是一个非常简单的操作,可能不需要任何 SQL 或记录集的使用。如果您选择记录集路线,我建议仅打开一次并保持其持久性,然后每次需要时导航它,而不是每次需要新图像时重复打开它。但如果我在做这件事,我会尽可能简单地为自己铺平道路,并选择使用自动编号 PK 路线来处理图像。如果您想在 SQL 中执行此操作,则为:
SELECT Images.ID, Images.Path
FROM Images
WHERE Images.ID = Round(100 * Rnd(), 0)
显然,您需要将100更改为适当的数字。如果您需要Randomize(),则用调用Randomize()并返回Round(100 * Rnd(), 0)
的函数替换直接的Round(100 * Rnd(), 0)
。
但也许我错过了一些重要的细节,这使得它比我所认为的要复杂得多。
Round(100 * Rnd(), 0)
的计算结果为57,但是没有ID = 57的行... - HansUp