random

amin1us

Member
من مي خوم بصورت رندوم يك ريكرد انتخاب كنم نمي دونيد با Sql بايد چي كار كنم??
لطفا كمك
 

amirlol

Well-Known Member
چند تا روش داری روش اول اینکه که بر حسب یک رکوردی عددی نمایش بده و اونو تغییر بده !!!
ولی راه بعدی بهتره

Public Function rando2()


Dim dbs As Database, rstYourRecordSet As Recordset
Dim rsCount As Long
Dim x() As Double
Set dbs = CurrentDb
Set rstYourRecordSet = dbs.OpenRecordset("YourTable")
dbs.Execute ("UPDATE YourTable SET random = Null")
rsCount = rstYourRecordSet.RecordCount
ReDim Preserve x(1 To rsCount)

Dim rx As Double, randu As Double, gset As Double
Dim ix As Long, iy As Long, iz As Long
Dim ncyc As Long, i As Long, j As Long, a As Long
Dim u As Long

ncyc = rsCount
i = ncyc + 1
Randomize Timer
ix = Int((Rnd * 133) + 1)
iy = Int((Rnd * 2345) + 1)
iz = Int((Rnd * 1030) + 1)
rx = 0#
For j = 1 To ncyc
x(j) = j
Next j
Do While i <> 2
i = i - 1
Call rando(ix, iy, iz, rx)
u = Int((rx * i) + 1)
a = x(i)
x(i) = x(u)
x(u) = a
Loop
While Not rstYourRecordSet.EOF
For j = 1 To ncyc

rstYourRecordSet.Edit
rstYourRecordSet("Random") = x(j)
rstYourRecordSet.Update
rstYourRecordSet.MoveNext
Next j
Wend

End Function


' *************************
'
Public Function rando(ix, iy, iz, randu)

Dim dx As Double, dy As Double, dz As Double, top As Double
Dim c1 As Double, c2 As Double, c3 As Double
Dim tmp As Long, l1 As Long, l2 As Long, l3 As Long
Dim r1 As Long, r2 As Long, r3 As Long

'
' Algorithm AS 183 Appl. Statist. (1982) vol.31, no.2
'
' Algorithm AS183 : An efficient and portable pseudo random
' number generator. Applied Statistics, 31, 188-190. op
' cit. An efficient and portable random number generator :
' Correction. Applied Statistics, 33, 123. op cit. 1987,
' May. Building a random number generator. Byte, pp.
' 127-128. Onghena, P. (1993). A theoretical and empirical
' comparison of mainframe, microcomputer, and pocket
' calculator pseudorandom number generators. Behavior
' Research Methods, Instruments, & Computers. 25, 384-395.

' Returns a pseudo-random number rectangularly distributed
' between 0 and 1. The cycle length is 6.95E+12 (See page 123
' of Applied Statistics (1984) vol.33), not as claimed in the
' original article.
' inputs are seed values IX,IY,IZ
' output is pseudo random number between 0 and 1
' which can obviously be converted to random integer
' r=int((randu*N)+.5) etc where randu is 0,1 and N is maximum integer desired
'
' IX, IY and IZ should be set to integer values between 1 and
' 30000 before the first entry.


l1 = 171
l2 = 172
l3 = 170
r1 = 30269
r2 = 30307
r3 = 30323
c1 = CDbl(r1)
c2 = CDbl(r2)
c3 = CDbl(r3)
ix = (l1 * ix) Mod r1
iy = (l2 * iy) Mod r2
iz = (l3 * iz) Mod r3
dx = CDbl(ix)
dy = CDbl(iy)
dz = CDbl(iz)

' Generate random uniform number
top = ((dx / c1) + (dy / c2) + (dz / c3))
tmp = Int(top)
randu = top - tmp
If randu < 0 Or randu > 1 Then
Beep
' Catastrophe
' end of world etc
End If
End Function


















یا







Sub Test()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

For intCount = 0 To 100000

strSQL = "INSERT INTO Table1 "
strSQL = strSQL & "(Field1)"
strSQL = strSQL & " VALUES ("
strSQL = strSQL & CLng(Rnd * (2 ^ 31)) & ");"

db.Execute strSQL

Next

db.Close

End Sub
 

rainynight

Member
فکر میکنم راه سوم بهتر باشه این همه دنگ و فنگ هم نداره :)
l select top 1 * from tablename order by NEWID() l
با این دستور شما یک رکورد را بصورت تصادف از بانک استخراج میکنی اگه بخوای بیشتر استخراج کنی اون یک رو به تعدادی که میخوای افزایش بده مثلا اگه 5 تا میخوای رندوم بیاری بزن select top 5
در حقیقت این دستور میاد هر دفعه به هر سطر یک id جدید نسبت میده و بر اساس شماره id سطر هارو مرتب میکنه بعد سطر اول رو استخراج میکنه
موفق باشید
 

desiran

Member
اقا این کار نمی کنه از این Newid() ایراد می گیره باید به جای این از چیزه دیگه ای بزارم
 

amirlol

Well-Known Member
من که تاحالا ندیدم چنین کدی
تو گوگل هم سرچ کردم
همه با error مواجه شده بودند
بهتره از آرایه ها کار کنی
 

جدیدترین ارسال ها

بالا