在SQL Server中选择N个随机记录而不重复 [英] Select N random records in SQL Server without repetition

问题描述

如何从表中一次选择N个随机记录而不重复以前由同一操作返回的记录？

显而易见的解决方案是：

  SELECT TOP 5 * FROM 
 MyTable 
 WHERE ID NOT IN（SELECT ID FROM previouslyReturned）
 ORDER BY newid（）
  

但是MyTable开始增长的时候效率会不会很低？

我有很长的记录清单，而且我一次需要五个记录来进行回合制游戏，而不必重复任何已经为给定游戏拉出的记录。因为我知道大约会有多少回合，所以我可以在比赛开始之前选择一个随机样本，这个样本非常大，但我宁愿它是动态的。我发现这个问题 a>，它使用随机种子和MySQL。

最终会有这么多的记录，重复不会是一个问题（记录>> N），但直到那么，我需要记录是独一无二的。在旁注中，我为我的持久层使用了Fluent NHibernate;也许NHibernate有一些功能允许这样做。

解决方案

不重复任何记录

在程序员之间不叫选择随机记录。所选的数值比你想象的要多得多，事实上，统计重复的次数和长度是统计学家检测作弊的一种方法。

你要找的东西叫做一个随机播放。混洗随机化一些有限的东西的顺序，如卡片或键。 （随机化行的顺序并不意味着与选择随机行相同的事情）。

在这种情况下，计划存储已经用于每个用户的密钥集。选择一组随机的行，但不在该集合中。有几种方法可以存储每个随机的行（键）。确保你可以知道哪一个是 last 或当前集合。

How do I select N random records from a table at a time without repetition of records previously returned by the same operation?

An obvious solution is:

SELECT TOP 5 * FROM
MyTable
WHERE Id NOT IN (SELECT Id FROM PreviouslyReturned)
ORDER BY newid()

But wouldn't that be really inefficient as MyTable starts to grow?

I have a long list of records and I require five records at a time for a turn-based game without repeating any of the records already pulled for the given game. Since I know approximately how many turns will take place, I could select a random sample before the game starts that is significantly large, but I would rather it be "dynamic". I found this question, which uses a random seed with MySQL.

Eventually there will be so many records that repetition won't be an issue (records >> N), but until then, I need records to be unique. On a sidenote, I use Fluent NHibernate for my persistence layer; perhaps NHibernate has some feature which allows this.

解决方案

without repeating any of the records

That's not called selecting random records among programmers. Values selected randomly repeat more often than you think, and in fact counting the number and length of repeats is one way statisticians detect cheating.

What you're looking for is called a shuffle. Shuffling randomizes the order of a finite set of things, like cards or keys. (Randomizing the order of rows doesn't mean the same thing as selecting random rows.)

In your case, plan to store the set of keys already used for each user. Select a random set of rows that aren't already in that set. There are several ways to store each random set of rows (keys); make sure you can tell which one is the last or current set.

这篇关于在SQL Server中选择N个随机记录而不重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！