代码之家 › 专栏 › 技术社区 › Joe Mastey

从每种类型中选择随机值

mysql

Joe Mastey · 技术社区 · 14 年前

我有两张桌子,评分:

+-----------+-----------+-------------+----------+
| rating_id | entity_id | rating_code | position |
+-----------+-----------+-------------+----------+
|         1 |         1 | Quality     |        0 |
|         2 |         1 | Value       |        0 |
|         3 |         1 | Price       |        0 |
+-----------+-----------+-------------+----------+

和评级选项

+-----------+-----------+------+-------+----------+
| option_id | rating_id | code | value | position |
+-----------+-----------+------+-------+----------+
|         1 |         1 | 1    |     1 |        1 |
|         2 |         1 | 2    |     2 |        2 |
|         3 |         1 | 3    |     3 |        3 |
|         4 |         1 | 4    |     4 |        4 |
|         5 |         1 | 5    |     5 |        5 |
|         6 |         2 | 1    |     1 |        1 |
|         7 |         2 | 2    |     2 |        2 |
|         8 |         2 | 3    |     3 |        3 |
|         9 |         2 | 4    |     4 |        4 |
|        10 |         2 | 5    |     5 |        5 |
|        11 |         3 | 1    |     1 |        1 |
|        12 |         3 | 2    |     2 |        2 |
|        13 |         3 | 3    |     3 |        3 |
|        14 |         3 | 4    |     4 |        4 |
|        15 |         3 | 5    |     5 |        5 |
+-----------+-----------+------+-------+----------+

我需要一个SQL查询(不是应用程序级别,必须保留在数据库中),它将随机选择一组分级。示例结果如下所示,但会为后续调用的每个评级ID选择一个随机值:

+-----------+-----------+------+-------+----------+
| option_id | rating_id | code | value | position |
+-----------+-----------+------+-------+----------+
|         1 |         1 | 1    |     1 |        1 |
|         8 |         2 | 3    |     3 |        3 |
|        15 |         3 | 5    |     5 |        5 |
+-----------+-----------+------+-------+----------+

我完全被困在随机的部分,按等级分组到目前为止是一个垃圾拍摄。有没有MySQLninjas想尝试一下?

谢谢, 乔

编辑:我尝试了一系列组合中的rand(),我确信有必要创建结果的随机性,但是我不知道如何为评级中的每一行返回一个随机行。我不能用 order by rand() limit 1 因为我需要三排 order by rand() limit 3 不会给我每人一个的 rating_id 这是最终的目标。我需要rand()和子查询或联接的组合,这样我就可以确保每个等级中的一个。

4 回复 | 直到 14 年前

Chad Birch 14 年前

好吧,有点乱,但似乎能完成任务。有人可能比我更清楚他们在做什么,这样可以清理:

SELECT random.rating_id, random.rand_option_id, r3.code, r3.value, r3.position
FROM
    (SELECT r.rating_id,
        (SELECT r2.option_id
         FROM rating_option r2
         WHERE r2.rating_id = r.rating_id
         ORDER BY RAND() LIMIT 1) AS 'rand_option_id'
     FROM rating_option r
     GROUP BY r.rating_id
    ) random
    LEFT JOIN rating_option AS r3 ON r3.option_id = rand_option_id

结果(当然,每次都不同):

+-----------+----------------+------+-------+----------+
| rating_id | rand_option_id | code | value | position |
+-----------+----------------+------+-------+----------+
|         1 |              4 |    4 |     4 |        4 |
|         2 |              6 |    1 |     1 |        1 |
|         3 |             13 |    3 |     3 |        3 |
+-----------+----------------+------+-------+----------+

-1

rayners 14 年前

你可以使用 rand() 函数在评分表上的选择中进行排序。

例如:

select rating_id from rating order by rand() limit 1

-1

DRapp 14 年前

正如你的评论和上面的其他帖子所阐明的那样

select * from rating_option order by rand()

将随机返回所有记录…但是,如果您只想要x数字,那么将其作为其他人注意到的限制。

select * from rating_option order by rand() limit 5 (or whatever number)

-1

gurun8 14 年前

你查过 rand() 功能?

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

http://www.petefreitag.com/item/466.cfm

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

样例代码

select *
from rating_option
group by rating_id
order by rand()