如何用T-SQL“选择”随机记录

This is a simple question that is actually hard to answer, because the "picking" has a special meaning.

这是一个很难回答的简单问题，因为“挑选”具有特殊含义。

I need to give three random picks for each person (and give pick/row number of 1, 2, and 3). What makes it hard is that the persons and picks are from different tables and there is no logical joining between the person and picks.

我需要为每个人提供三个随机选择（并给出选择/行号1,2和3）。难以理解的是，这些人和选秀权来自不同的牌桌，并且这些人和选秀权之间没有逻辑联系。

The closest I can get is:

我能得到的最接近的是：

SELECT TOP 15 database_id, create_date, RowNo, cs.name FROM sys.databases
CROSS apply ( 
  SELECT top 3 Row_number()OVER(ORDER BY (SELECT NULL)) AS RowNo,*
  FROM (SELECT top 3 name from sys.all_views ORDER BY NEWID()) T
  ) cs

I know the above is not person and picks, but it a working SQL that anyone can test it out without creating person and picks tables first. And,

我知道以上不是人和选择，但它是一个工作的SQL，任何人都可以测试它而不创建人和首先选择表。和，

It illustrates the problem I'm facing --

它说明了我面临的问题 -

the above SQL will give each person the same picks, whereas I need to give different person different picks.

上面的SQL会给每个人提供相同的选择，而我需要给不同的人提供不同的选择。

How to do that? Thx.

怎么做？谢谢。

3 个解决方案

#1

Adding a correlated condition inside the CROSS APPLY will solve your problem

在CROSS APPLY中添加相关条件将解决您的问题

SELECT TOP 15 database_id,
              create_date,
              RowNo,
              cs.NAME
FROM   sys.databases d
       CROSS apply (SELECT TOP 3 Row_number() OVER(ORDER BY (SELECT NULL)) AS RowNo, *
                    FROM   (SELECT TOP 3 NAME
                            FROM   sys.all_views v
                            WHERE  d.NAME = d.NAME --Here
                            ORDER  BY Newid()) T) cs

Check the alias name in Where clause both LHS and RHS are from same table and same column it is just to execute the sub-query for each row in databases table

检查Where子句中的别名，LHS和RHS都来自同一个表，同一列只是为了对数据库表中的每一行执行子查询

#2

Modifying your own answer slightly will do the job. Check this.

稍微修改自己的答案就可以了。检查一下。

SELECT TOP 15 database_id, create_date, RowNo, cs.name FROM sys.databases
CROSS apply ( 
  SELECT top 3 Row_number()OVER(ORDER BY NEWID()) AS RowNo,*
  FROM (SELECT top 3 name from sys.all_views ORDER BY NEWID()) T
  ) cs

The only change that I have done is to replace the NULL with NEWID().

我所做的唯一更改是用NEWID（）替换NULL。

#3

-5

Random function is available in Sql language could use it to randomly pick a record id in a range of record ids found in the source table.

Sql语言中的随机函数可以使用它随机选择源表中找到的记录ID范围内的记录ID。

In a newer sql version I think this may work but I can't test it currently. Older sql version will not support the by rand() command. Try this let me know if it works. Later this week I can get you something that will work on sql 2000 and up. I had to do this years ago. Let me know if this works on your Sql 2008.

在较新的sql版本中，我认为这可能有效但我目前无法测试它。较旧的sql版本不支持by rand（）命令。试试这个让我知道它是否有效。本周晚些时候，我可以为你提供一些适用于sql 2000及更高版本的东西。几年前我不得不做这件事。如果这适用于您的Sql 2008，请告诉我。

select top 3 from table_name order by rand()

从table_name命令中选择前3个按rand（）

#1