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