問題描述

對于像這樣的簡單 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的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！