MYSQL select random of each of the categories

2019-06-12 03:50发布

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

table name: t_shop

column name: 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

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.

标签: mysql random
2条回答
倾城 Initia
2楼-- · 2019-06-12 04:08
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;

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 |
+---------+-----------------+--------+-----------------------+

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.


Re your comment:

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.

查看更多
ゆ 、 Hurt°
3楼-- · 2019-06-12 04:20

If you have two categories (as in your question), the easiest way in MySQL is to use union all:

(select * from t_shop where category = 1 order by rand() limit 5)
union all
(select * from t_shop where category = 2 order by rand() limit 5)
查看更多
登录 后发表回答