sql选择随机的记录

选择随机的记录
A number of other writers have suggested using a client-side cursor, loading the table and then using move by Rand()*recordcount to get a random record. This works well for small datasets or choosing just a few records, but when 20 or more records need to be selected from a large database, there can be a large performance hit.

The Rand() function in SQL seems ideal for the task, but the problem is that if used thus:

SELECT TOP 5 Questions.*, Rand() as ID
FROM Questions

the ID field always has the same number in it (ie: the Rand() was only evaluated once).

I have found an alternative method that seems to ''trick'' the backend into evaluating the expression each time. It involves changing the seed for each record and is easiest to use if there is an AutoInc field in the table:

SELECT TOP 20 Questions.*
FROM Questions
ORDER BY (Rand(Questions.QID))

where QID is an AutoInc field. This does produce a random list quite easily and on my testing the list does seem to be reasonably random.

Note: Using MS Access - Rnd() is the function, not Rand()