要得到一个0到(N-1)之间的随机数，可以使用.

abs(checksum(newid())) % N

这意味着获得正值 1-N，您使用

1 + abs(checksum(newid())) % N

注意:RAND() 不起作用 - 每个查询批次评估一次，并且您会遇到 tableA 的所有行的相同值.>

查询:

SELECT *从表A AJOIN (select *, rn=row_number() over (order by newid())来自 tableB) B ON B.rn <= 1 + abs(checksum(newid())) % 9

(假设您希望每个 A 最多有 9 个随机的 B 行)

I have two tables where the data is not related For each row in table A i want e.g. 3 random rows in table B

This is fairly easy using a cursor, but it is awfully slow

So how can i express this in single statement to avoid RBAR ?

To get a random number between 0 and (N-1), you can use.

abs(checksum(newid())) % N

Which means to get positive values 1-N, you use

1 + abs(checksum(newid())) % N

Note: RAND() doesn't work - it is evaluated once per query batch and you get stuck with the same value for all rows of tableA.

The query:

SELECT *
  FROM tableA A
  JOIN (select *, rn=row_number() over (order by newid())
          from tableB) B ON B.rn <= 1 + abs(checksum(newid())) % 9

(assuming you wanted up to 9 random rows of B per A)