获取随机记录的 ROW NUMBER

get ROW NUMBER of random records(获取随机记录的 ROW NUMBER)

本文介绍了获取随机记录的 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 

这篇关于获取随机记录的 ROW NUMBER的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本文标题为:获取随机记录的 ROW NUMBER

基础教程推荐