access mdb 执行多行sql

access随机查几行

This is the first article on row numbers in Microsoft Access.

这是有关Microsoft Access中行号的第一篇文章。

The second is about Sequential Rows in Microsoft Access.

第二个关于Microsoft Access中的顺序行 。

The third is about Ranking rows in Microsoft Access.

第三是关于在Microsoft Access中对行进行排名 。

()

(What is it for?)

Typically, the reason for assigning a random number to each record of a recordset is to be able to sort these in a sequence completely out of any normal order - like ID, customer number, purchase date, etc. As the assigned numbers are random, when sorting on these, any other order will appear to be random.

通常，为记录集的每个记录分配一个随机数的原因是为了能够按照完全不按任何常规顺序（例如ID，客户编号，购买日期等）的顺序对它们进行排序。由于分配的编号是随机的，当对这些进行排序时，任何其他顺序似乎都是随机的。

That's the basics. Next question is why or for what purpose?

这就是基础。 下一个问题是为什么还是出于什么目的 ？

That is usually to select a small count of records from a large set of records where you don't want these to be sorted by something - it could be for statistics, sample quality control, or similar.

通常是从大量记录中选择少量记录，在这些记录中您不希望这些记录按某种顺序进行排序-可能是出于统计，样品质量控制或类似目的 。

(Random, or just apparently random)

It is important to understand, that generating truly random numbers is an art of its own, that still can keep mathematicians busy: Randomness.

重要的是要理解，生成真正的随机数本身就是一门艺术，仍然会使数学家忙于： 随机性 。

Luckily, for our purpose, a less-than-ideal method can be used: pseudo-random numbers. This is what the native function of VBA, Rnd, offers.

幸运的是，出于我们的目的，可以使用一种不理想的方法： 伪随机数 。 这就是VBA Rnd的本机功能。

What this means is, that even if the generated numbers may appear random, they are not. They are picked from a long pre-generated list of numbers having an even distribution between 0 and 1. This means, that if you sum these and calculate the average, the result will be close to 0.5.

这意味着，即使生成的数字可能看起来是随机的，也不是随机的。 它们是从一长串预先生成的数字中选取的，这些数字的平均分布在0和1之间。这意味着，如果将它们相加并计算平均值，结果将接近0.5。

This you can verify quickly. Type into the Immediate Window this line:

您可以快速验证。 在立即窗口中输入以下行：

s = 0 : For i = 1 to 1000000 : s = s + Rnd() :Next : ? s / i

and press Enter, and you will get results like these:

然后按Enter键，您将获得以下结果：

0.500089374910625

0.499534094215906

0.499724781525218

0.49985175014825

0.500306843443157

0.500089374910625

0.499534094215906

0.499724781525218

0.49985175014825

0.500306843443157

All values are, as seen, very close to 0.5.

如图所示，所有值都非常接近0.5。

However, as the values are picked from a sequence, the first pick must start somewhere, and - if no precaution is taken - that will be from the start. The first five values can easily be listed:

但是，当从一个序列中选取值时，第一次选择必须从某个地方开始，并且-如果没有采取预防措施，则必须从头开始。 可以轻松列出前五个值：

For i = 1 to 5 : ? Rnd() : Next

If you open the application and run this code, those five values will always be:

如果打开应用程序并运行此代码，则这五个值将始终为：

0.7055475

0.533424

0.5795186

0.2895625

0.301948

0.7055475

0.533424

0.5795186

0.2895625

0.301948

Having the same values returned defeats any purpose of random. Thus, a command, Randomize, exists that will set the start point to somewhere in the predefined sequence of pseudo-random numbers and that somewhere is controlled by the time of the day. 

返回相同的值会打败任何随机目的。 因此，一个命令， 随机化 ，存在将设置起点到某处的伪随机数的所述预定义序列和某处由一天中的时间来控制。

This still isn't random, though you would have to open the application and run the command at the exact same split second of the day, which is not likely to happen.

这仍然不是随机的，尽管您必须在一天中的同一时间瞬间打开应用程序并运行命令，但这不太可能发生。

To conclude, to obtain five seemingly random numbers, call the code like this:

最后，要获得五个看似随机的数字，请调用以下代码：

Randomize
For i = 1 to 5 : ? Rnd() : Next

(Random values in queries)

Until now, code has been VBA only, not SQL. And while you can use a function - user-defined or native of VBA - in an Access query, a statement, which is what Randomize is, can not. To overcome this limitation, you can create a user-defined function that calls Randomize.

到目前为止，代码仅是VBA，而不是SQL。 尽管可以在Access查询中使用用户定义的功能或VBA的本机功能，但是语句（即Randomize是）不能。 要克服此限制，可以创建一个调用Randomize的用户定义函数。

Or, you can call Rnd() with a parameter having a negative value. Again, this parameter value could be generated by Timer()  to obtain different start points for the value series returned:

或者，您可以使用具有负值的参数调用Rnd（） 。 同样，此参数值可以由Timer（）生成，以获取返回的值系列的不同起点：

Select *, Rnd(-Timer()) As RandomNumber From YourTable

But this has two issues. First and most important, it will return the same number for all records, as the expression will be called once only; second, it will not return values with an even distribution between 0 and 1.

但这有两个问题。 首先，也是最重要的一点，它将为所有记录返回相同的数字，因为该表达式仅被调用一次； 第二，它将不会返回0到1之间均匀分布的值。

The uneven distribution can easily be demonstrated:

不均匀分布很容易证明：

s = 0 : For i = 1 to 1000000 : s = s + Rnd(-Timer()) : Next : ? s / i

This will not result in average values close to 0.5. In fact, they can vary between 0.2 and 0.8!

这不会导致平均值接近0.5。 实际上，它们可以在0.2到0.8之间变化！

To solve both issues, include the primary key (typically [ID]) in the expression:

要解决这两个问题，请在表达式中包括主键 （通常为[ID]）：

Select *, Rnd(-Timer() * [ID]) As RandomNumber From YourTable

This will force a call of the expression for each record, and it will make the distribution even - which you easily can check out:

这将强制为每个记录调用该表达式，并使分布均匀-您可以轻松检出：

s = 0 : For i = 1 to 1000000 : s = s + Rnd(-Timer() * i) : Next : ? s / i

The returned average will now be very close to the desired value of 0.5.

现在返回的平均值将非常接近期望值0.5。

(Random sorting of a query)

At this point, we should be able to apply some random order to a query. For example, using the Order Details table from the attached demo, holding a few tables from the Northwind 2007 sample database from Microsoft.

在这一点上，我们应该能够对查询应用一些随机顺序。 例如，使用所附演示中的“ 订单详细信息”表，保存来自Microsoft的Northwind 2007示例数据库的一些表。

SELECT 
    ID, 
    [Order ID], 
    [Unit Price], 
    Rnd(-Timer()*[ID]) AS RandomRecord
FROM 
    [Order Details]
ORDER BY 
Rnd(-Timer()*[ID]);

Run it, and you'll see the records in some random order as determined by the generated values in the field RandomRecord - all fine and dandy - except for one thing which will be either a major advantage or a major disadvantage:

运行它，您将看到随机记录的记录，这些记录由RandomRecord字段中的生成值确定-很好，很花俏-除了一件事可能是主要优点或主要缺点是：

Whenever the records are required, resorted, or refiltered, all values of field RandomRecord will be regenerated.

无论何时需要，重新整理或重新过滤记录，都将重新生成字段RandomRecord的所有值。

If this is desired is, of course, determined by the purpose of the query. 

当然，这是由查询的目的确定的。

If it is, you are all set. If not, another method must be used.

如果是这样，您就一切就绪。 如果不是，则必须使用另一种方法。

(Persistent random sorting of a query)

If your scenario requires what would seem to be persistent random numbers, an external function must be used in an expression for a field in the query. This will generate - and keep - a pseudo-random number for each record. 

如果您的方案需要看似持久的随机数 ，则必须在表达式中为查询中的字段使用外部函数。 这将为每个记录生成并保留一个伪随机数。

What this means is, that - once generated - the random numbers will resist any change in ordering or filtering of the query, even a requery.

这意味着，一旦生成，随机数将抵抗查询顺序或过滤的任何更改，甚至重新查询。

The method to collect the numbers is to use - a collection. For each key, a random number is generated and collected bound to its key. It takes only a few lines of code:

收集号码的方法是使用-收集。 对于每个密钥，都将生成一个随机数，并将其绑定到其密钥。 它只需要几行代码：

' Builds random row numbers in a select, append, or create query
' with the option of a initial automatic reset.
'
' 2018-09-11. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RandomRowNumber( _
    ByVal Key As String, _
    Optional Reset As Boolean) _
    As Single
    ' Error codes.
    ' This key is already associated with an element of this collection.
    Const KeyIsInUse        As Long = 457
   
    Static Keys             As New Collection
 
    On Error GoTo Err_RandomRowNumber
   
    If Reset = True Then
        Set Keys = Nothing
    Else
        Keys.Add Rnd(-Timer * Keys.Count), Key
    End If
   
    RandomRowNumber = Keys(Key)
   
Exit_RandomRowNumber:
    Exit Function
   
Err_RandomRowNumber:
    Select Case Err
        Case KeyIsInUse
            ' Key is present.
            Resume Next
        Case Else
            ' Some other error.
            Resume Exit_RandomRowNumber
    End Select
End Function

Now, create a query like this - again using the table from the attached demo database:

现在，创建这样的查询-再次使用所附演示数据库中的表：

SELECT
    ID,
    [Order ID],
    [Unit Price],
    RandomRowNumber(CStr([ID])) AS RandowRow
FROM
    [Order Details]
ORDER BY
    RandomRowNumber(CStr([ID]));

Note, that you can change ordering and filtering back and forth - you can even close the query and open again - the values of RandomRow will remain.

请注意，您可以来回更改顺序和过滤-甚至可以关闭查询并再次打开-RandomRow的值将保留。

Often, however, you will want the numbers of RandomRow to regenerate whenever the query is run. To obtain this, call the function to reset the numbers either before or after running the query:

但是，通常，无论何时运行查询，您都希望重新生成RandomRow的数量。 为此，请在运行查询之前或之后调用函数以重置数字：

RandomRowNumber vbNullString, True

You may feel tempted to include that in the query for an automatic reset when the query is run (the WHERE clause):

您可能会想在查询运行时将其包括在查询中以进行自动重置（WHERE子句）：

SELECT
    ID,
    [Order ID],
    [Unit Price],
    RandomRowNumber(CStr([ID])) AS RandowRow
FROM
    [Order Details]
WHERE 
RandomRowNumber(CStr([ID]))<>RandomRowNumber("",True)
ORDER BY
    RandomRowNumber(CStr([ID]));

but the drawback is, that now the query will behave like the query listed under the previous paragraph; it will regenerate the numbers whenever you change sorting or filtering or requery the query.

但是缺点是，现在查询的行为类似于上一段中列出的查询； 每当您更改排序或过滤或重新查询查询时，它将重新生成数字。

(Random records in a form)

This is the typical use for random numbers: List a count of randomly selected items.

这是随机数的典型用法： 列出随机选择的项目的数量 。

For example, to list ten random products from the Northwind 2007 sample database, create this query:

例如，要列出 Northwind 2007示例数据库中的十个随机产品 ，请创建以下查询：

SELECT TOP 10 
    *, 
    RandomRowNumber(CStr([ID])) AS RandomRow, 
Rnd(-Timer()*[ID]) AS RandomRecord
FROM 
    Products 
ORDER BY 
    RandomRowNumber(CStr([ID]));

and use it as RecordSource for a form.

并将其用作表单的RecordSource。

Note please, that the expression for RandomRecord is included as well. Its only purpose here is to demonstrate that is often will requery, while RandomRow does not.

请注意，还包括RandomRecord的表达式。 它的唯一目的是证明经常会重新查询，而RandomRow不会。

The form may look like this:

该表格可能如下所示：

Note the sorting on field Row causing all other fields to appear in random order.

请注意，对“ 行”字段进行排序会导致所有其他字段以随机顺序出现。

A form of this type displays a selection only. For many reasons, you may wish to view another selection. A normal requery will - as explained - not change it, so a dedicated button is included: Reset.

此类型的表单仅显示选择。 由于许多原因，您可能希望查看其他选择。 如所解释的那样，普通的重新查询不会更改它，因此包含一个专用按钮： Reset 。

Clicking this runs this code:

单击此将运行以下代码：

Private Sub ResetRandomButton_Click()

    RandomRowNumber vbNullString, True
    Me.Requery

End Sub

First, it resets the numbers, then it requeries the form and its RecordSource, the query.

首先，它重置数字，然后重新查询表单及其RecordSource，即查询。

The query will again call the function RandomNumber for each record, causing the numbers to be rebuilt. As the numbers will be different from the previous values, and the form is ordered by these numbers, the displayed selection of records will change.

该查询将再次为每个记录调用函数RandomNumber，导致重新构建数字。 由于数字将不同于先前的值，并且表格是根据这些数字排序的，因此显示的记录选择将发生变化。

(Conclusion)

With these functions and example queries - and the basics on how pseudo-random numbers are handled by VBA - you should have a solid foundation for selecting the right tools and methods when records are to be randomly selected or ordered in an application.

有了这些功能和示例查询，以及有关VBA如何处理伪随机数的基础，您应该为在应用程序中随机选择记录或对记录进行排序时选择正确的工具和方法打下坚实的基础。

Combined with the second part on sequential enumeration, they are supposed to cover every real-life need.

结合顺序枚举的第二部分，它们应该可以满足每一个现实生活中的需求。

(Further information)

The random numbering is part of a project on the general numbering of records.

随机编号是项目中记录总编号的一部分。

Sequential ordering is covered here: Sequential Rows in Microsoft Access

此处介绍了顺序排序： Microsoft Access中的顺序行

Ranking rows is covered here: Ranking rows in Microsoft Access

此处包含排名行 ： Microsoft Access中的排名行

A sample database in Access 2016 is attached: RowNumbers 1.2.0.zip

随附了Access 2016中的示例数据库： RowNumbers 1.2.0.zip

All code can also be found on GitHub : VBA.RowNumbers

所有代码也可以在GitHub上找到 ： VBA.RowNumbers

I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.

希望本文对您有所帮助。 鼓励您在下面提出问题，报告任何错误或对此作出任何其他评论。

Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.

注意 ：如果您需要有关此主题的更多“支持”，请考虑使用Experts Exchange 的“提问”功能。 我会监督提出的问题，并很高兴与其他电子工程师一起为以这种方式提出的问题提供所需的任何其他支持。

Please do not forget to press the "Thumbs Up" button if you think this article was helpful and valuable for EE members.

如果您认为本文对EE成员有用且有价值，请不要忘记按下“竖起大拇指”按钮。

翻译自: https://www.experts-exchange.com/articles/33030/Random-Rows-in-Microsoft-Access.html

access随机查几行