获取随机记录的 ROW NUMBER

2021-09-10 00:00:00 sql tsql sql-server

对于像这样的简单 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 

相关文章