SQL为每个年龄返回100个随机行 [英] SQL return 100 random rows for each age
问题描述
SELECT，i.name，i。 Gender，i.Age，i.MP，b.score
FROM i
INNER JOIN b 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
目前上述查询有效，但它只返回100个随机行，年龄为0。公平的几个例子，但不能确定我是否应该使用一个组或可能是一个循环。表中有超过10,000,000行。
您可以使用下面的用户定义变量查询会为每个不同的记录提供一条记录年龄，内部查询只是检查相同的年龄，并给予他们的排名，像4个相同年龄（年龄= 1）的排名将是1,2,3,4，并且当年龄= 2时，排名数字将从1开始并且其中外部查询的过滤器正在过滤行以显示where排名为1，因此对于每个不同的年龄，您将获得一行，并且它们随机排序。 c $ c> 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
$ b假设你的连接查询给你的结果为
样本数据集
姓名性别年龄Mp分数
================== ==========
test1男1 1 10
test2男1 1 10
test3男1 1 10
test4男2 1 10
test5男2 1 10
test6男3 1 10
test7男4 1 10
test8男4 1 10
.....
现在在我的答案中使用内部查询将为您提供排序列如下的结果集
内部查询结果集
名字性别年龄Mp分数排名
======== ==========================
test2男1 1 10 1
test1男1 1 10 2
test3男1 1 10 3
test4男2 1 10 1
test5男2 1 10 2
test6男3 1 10 1
test7男4 1 10 1
test8男4 1 10 2
正如上面的结果集，你可以看到年龄= 1有3行，它们的排名是不同的1,2,3同样的例子，对于年龄= 2的排名，对于年龄= 4，1,2是相同的。现在在我的回答中，外部查询将过滤掉rank = 1的结果，所以最终结果集将包含每个不同的一行，如下所示结果集
最终输出
$ pre> 姓名性别年龄Mp分数等级
==================================
test2男1 1 10 1
test4男2 1 10 1
test6男3 1 10 1
test7男4 1 10 1
随机排序部分在内部查询中完成，您可以按部分 ORDER BY i.Age，RAND （）它将首先按年龄顺序排列，然后对于相同的年龄值，它会进一步随机排列结果。希望它是有道理的
For each of the i.Age(0-100) I want to return 100 random rows for each age with the selected data. I am unsure if I should be using a while loop to accomplish this or possibly a group by with a limit somehow? I've looked at a fair few different examples but I'm still quite confused.
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
At the moment the above query works but it only returns 100 random rows for the age of 0. I've looked at a fair few examples but cannot grasp whether I should use a group by or possibly a while loop. There are over 10,000,000 rows in the table.
You can use user defined variables below query will give you one record per each distinct age,inner query is just checking the for same ages and giving rank to them like for 4 same ages (age = 1) rank will be 1,2,3,4 and when age =2 then rank number will start from 1 again and where filter of outer query is filtering rows to show the where rank is 1 so for each distinct age you will get one row and they are ordered randomly
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
let suppose your joined query give you the results as
Sample data set
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
.....
Now using the inner query in my answer will give you the result sets with rank column as below
Inner query result set
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
As in above result set you can see for age = 1 have 3 rows and their ranks are different 1,2,3 same example for age = 2 ranks are 1,2 same for age = 4,Now in my answer the outer query will filter out the result where rank = 1 so the final result set will contain one row for each distinct as see below result set
Final output
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
The random ordering part is done at the inner query as you can see the order by part ORDER BY i.Age, RAND() it will first order the age in ascending manner and then for same age values it will further order results randomly.Hope it makes sense
这篇关于SQL为每个年龄返回100个随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
