SQL:如何从一个表中为另一个表中的每一行获取随机数的行 [英] SQL: how to get random number of rows from one table for each row in another

问题描述

我有两个数据不相关的表对于表 A 中的每一行，我想要例如表B中的3个随机行

使用游标相当容易，但速度非常慢

那么我如何在单个语句中表达这一点以避免 RBAR ?

解决方案

要得到一个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)

这篇关于SQL:如何从一个表中为另一个表中的每一行获取随机数的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！