MYSQL select random of each of the categories

问题:

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.

回答1:

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)

回答2:

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.