获取随机记录的 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
相关文章