MYSQL从每个类别中随机选择 [英] MYSQL select random of each of the categories

问题描述

我想从同一表格的每个类别中随机选择5条记录.

I want to select random 5 records from each categories in the same table.

表格名称:t_shop

table name: t_shop

列名:shop_id，shop_categoryID

column name: shop_id, shop_categoryID

例如: shop_id | shop_categoryID

For example: shop_id | shop_categoryID

1 | 1
2 | 1
5 | 1
7 | 1
9 | 1
10| 1
13| 2
15| 2
22| 2
23| 2
25| 2

我尝试在子查询中使用限制，但发生错误:此版本的MySQL尚不支持'LIMIT& IN/ALL/ANY/SOME子查询

I have tried using limit in subquery, but an error occur: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery

我想问一下有什么办法解决吗?谢谢.

I would like to ask are there any ways to solve? Thanks.

推荐答案

SELECT STRAIGHT_JOIN t.* 
FROM (SELECT @r:=0, @c:= null) AS rownum_init,
   (SELECT *, (@r:=IF(@c=shop_categoryID, @r+1, 1)) AS rownum, (@c:=shop_categoryID) 
    FROM t_shop
    ORDER BY shop_categoryID, RAND()) AS t
WHERE t.rownum <= 5;

以下是使用测试数据在MySQL 5.5.30上进行测试的结果:

Here's the output in my test on MySQL 5.5.30, using your test data:

+---------+-----------------+--------+-----------------------+
| shop_id | shop_categoryID | rownum | (@c:=shop_categoryID) |
+---------+-----------------+--------+-----------------------+
|       5 |               1 |      3 |                     1 |
|       1 |               1 |      1 |                     1 |
|       9 |               1 |      5 |                     1 |
|       2 |               1 |      2 |                     1 |
|       7 |               1 |      4 |                     1 |
|      22 |               2 |      3 |                     2 |
|      23 |               2 |      4 |                     2 |
|      25 |               2 |      5 |                     2 |
|      13 |               2 |      1 |                     2 |
|      15 |               2 |      2 |                     2 |
+---------+-----------------+--------+-----------------------+

通常，有关任何涉及ORDER BY ... RAND()的解决方案的建议是它不可扩展，并且随着表大小的增加，它的成本也越来越高.但是，对于您所描述的复杂的随机选择问题，它仍然可能是解决该问题的唯一方法.如果表现不佳，那就不要惊讶.

Usually the advice about any solution involving ORDER BY ... RAND() is that it's not scalable, and it becomes more costly as the size of your table grows. But for complex random selection problems like the one you describe, it may nevertheless be the only way to solve the problem. Just don't be surprised if it performs poorly.

发表您的评论

我不明白你的意思. Rownum在每个类别中都有一个从1开始的独特值，然后在类别更改时从1开始.

I don't understand what you mean. Rownum has a distinct value starting at 1 in each category, then it starts over at 1 as the category changes.

这篇关于MYSQL从每个类别中随机选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！