大表上的MYSQL SELECT随机在ORDER BY SCORE上 [英] MYSQL SELECT random on large table ORDER BY SCORE

问题描述

我有一个大约25000行的大型mysql表.有5个表格字段:ID，NAME，SCORE，AGE，SEX

I have a large mysql table with about 25000 rows. There are 5 table fields: ID, NAME, SCORE, AGE,SEX

我需要按SCORE DESC随机选择5个MALES订单

I need to select random 5 MALES order BY SCORE DESC

例如，如果有100个人的得分为60，而另外100个人的得分为45，则脚本应从25000个列表中的前200个人中随机返回5

For instance, if there are 100 men that score 60 each and another 100 that score 45 each, the script should return random 5 from the first 200 men from the list of 25000

按RAND()订购

非常慢

ORDER BY RAND()

is very slow

真正的问题是，这5个人应该是前200条记录中的一个随机选择.感谢您的帮助

The real issue is that the 5 men should be a random selection within the first 200 records. Thanks for the help

推荐答案

所以要获得类似的信息，我将使用子查询. .

so to get something like this I would use a subquery.. that way you are only putting the RAND() on the outer query which will be much less taxing.

根据我对您的问题的了解，您希望桌前200名得分最高的男性...这样的话:

From what I understood from your question you want 200 males from the table with the highest score... so that would be something like this:

SELECT * 
FROM table_name
WHERE age = 'male'
ORDER BY score DESC
LIMIT 200

现在要随机分配5个结果，就是这样.

now to randomize 5 results it would be something like this.

SELECT id, score, name, age, sex
FROM
(   SELECT * 
    FROM table_name
    WHERE age = 'male'
    ORDER BY score DESC
    LIMIT 200
) t -- could also be written `AS t` or anything else you would call it
ORDER BY RAND()
LIMIT 5

这篇关于大表上的MYSQL SELECT随机在ORDER BY SCORE上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！