SQL为每个年龄返回100个随机行

对于每个 i.Age(0-100)，我想使用所选数据为每个年龄返回 100 个随机行。我不确定是否应该使用 while 循环来完成此操作，或者是否应该使用带有限制的 group by 来完成此操作？我看过很多不同的例子，但我仍然很困惑。

SELECT, i.name, i.Gender, i.Age, i.MP, b.score
FROM i
INNER JOIN b on b.name=i.name
WHERE i.MP='F'
AND i.gender='F'
AND b.score<=-1
AND i.age = 0
ORDER BY RAND()
LIMIT 100

目前，上面的查询可以工作，但它只返回年龄为 0 的 100 个随机行。我已经查看了相当多的示例，但无法掌握是否应该使用 group by 或可能使用 while 循环。表中有超过 10,000,000 行。

您可以使用下面的用户定义变量，查询将为每个不同年龄提供一条记录，内部查询只是检查相同年龄并为它们提供排名，例如 4 个相同年龄（年龄 = 1）排名将为 1,2,3 ,4，当年龄=2时，排名数字将再次从1开始，外部查询的过滤器正在过滤行以显示其中排名为1，因此对于每个不同的年龄，您将得到一行，并且它们是随机排序的

SELECT  c.name, c.Gender, c.Age, c.MP, c.score
FROM (
SELECT i.name, i.Gender, i.Age, i.MP, b.score,
@r:= CASE WHEN @g = i.Age THEN @r + 1 ELSE 1 END rownum,
@g:=i.Age
FROM i
INNER JOIN b ON b.name=i.name
CROSS JOIN(SELECT @g:=NULL ,@r:=0) a
WHERE i.MP='F' AND i.gender='F' AND b.score<=-1
ORDER BY  i.Age, RAND()
) c
WHERE c.rownum = 1
ORDER BY c.Age
LIMIT 100

假设您的连接查询给出的结果为

样本数据集

name   gender  Age  Mp score
============================
test1  male    1    1   10
test2  male    1    1   10
test3  male    1    1   10
test4  male    2    1   10
test5  male    2    1   10
test6  male    3    1   10
test7  male    4    1   10
test8  male    4    1   10
.....

现在，在我的答案中使用内部查询将为您提供带有排名列的结果集，如下所示

内查询结果集

name   gender  Age  Mp score rank
==================================
test2  male    1    1   10    1
test1  male    1    1   10    2
test3  male    1    1   10    3
test4  male    2    1   10    1
test5  male    2    1   10    2
test6  male    3    1   10    1
test7  male    4    1   10    1
test8  male    4    1   10    2

如上面的结果集所示，您可以看到年龄 = 1 有 3 行，它们的排名不同 1,2,3 年龄 = 2 的相同示例，年龄 = 4 的排名为 1,2 相同，现在在我的答案中，外部查询将过滤掉排名 = 1 的结果，因此最终结果集将包含每个不同的一行，如下结果集所示

最终输出

name   gender  Age  Mp score rank
==================================
test2  male    1    1   10    1
test4  male    2    1   10    1
test6  male    3    1   10    1
test7  male    4    1   10    1

随机排序部分是在内部查询中完成的，您可以看到按部分排序ORDER BY i.Age, RAND()它将首先以升序方式对年龄进行排序，然后对于相同的年龄值，它将进一步随机对结果进行排序。希望它有意义