获取随机记录的 ROW NUMBER

问题描述

对于像这样的简单 SQL，

For a simple SQL like,

SELECT top 3 MyId FROM MyTable ORDER BY NEWID()

如何给它们添加行号，使行号变成 1,2 和 3?

how to add row numbers to them so that the row numbers become 1,2, and 3?

更新:

我以为我可以像上面一样简化我的问题，但事实证明它更复杂.所以这是一个更完整的版本——我需要为每个人随机选择三个(来自 MyTable)，选择/行号为 1、2 和 3，并且之间没有逻辑连接人选.

I thought I can simplify my question as above, but it turns out to be more complicated. So here is a fuller version -- I need to give three random picks (from MyTable) for each person, with pick/row number of 1, 2, and 3, and there is no logical joining between person and picks.

SELECT * FROM Person
LEFT JOIN (
  SELECT top 3 MyId FROM MyTable ORDER BY NEWID()
) D ON 1=1

上述SQL的问题是，

• 显然，应添加选择/行号 1、2 和 3
• 不明显的是，上面的SQL会给每个人相同的选择，而我需要给不同的人不同的选择

• Obviously, pick/row number of 1, 2, and 3 should be added
• and what is not obvious is that, the above SQL will give each person the same picks, whereas I need to give different person different picks

这是一个有效的 SQL 来测试它:

Here is a working SQL to test it out:

SELECT TOP 15 database_id, create_date, 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

所以，请帮忙.

注意:这不是关于 MySQL byt T-SQL，因为它们的语法不同，因此解决方案不同 也是.

NOTE: This is NOT about MySQL byt T-SQL as their syntax are different, Thus the solution is different as well.

推荐答案

将 Row_number 添加到外部查询.试试这个

Add Row_number to outer query. Try this

SELECT Row_number()OVER(ORDER BY (SELECT NULL)),*
FROM   (SELECT TOP 3 MyId
        FROM   MyTable
        ORDER  BY Newid()) a 

逻辑上TOP关键字在Select之后处理.生成行号后，将随机抽取 3 条记录.所以你不应该在原始查询中生成 Row Number

Logically TOP keyword is processed after Select. After Row Number is generated random 3 records will be pulled. So you should not generate Row Number in original query

更新

可以通过CROSS APPLY来实现.将 cross apply where 子句中的列名替换为 Person 表

It can be achieved through CROSS APPLY. Replace the column names inside cross apply where clause with valid column name from Person table

SELECT *
FROM   Person p
       CROSS apply (SELECT Row_number()OVER(ORDER BY (SELECT NULL)) rn,*
                    FROM   (SELECT TOP 3 MyId
                            FROM   MyTable
                            WHERE  p.some_col = p.some_col -- Replace it with some column from person table
                            ORDER  BY Newid())a) cs