每个指定的组选择n个随机行

本文介绍了每个指定的组选择n个随机行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在努力寻找以下问题的最佳解决方案. 假设我有一个像这样的表"Table":

I am struggling to find an optimal solution for the following problem. Suppose I have a table 'Table' like this:

id name report_id 1 name1 1 2 name2 3 3 name3 5 4 name1 7 5 name3 8 ....................

我想为集合中的每个值选择:('name1'，'name2')10个随机唯一行. 当然也可以像这样进行联合:

I want to select for each value in a set: ('name1', 'name2') 10 random unique rows. Of course it is possible to do with union like:

(SELECT * FROM Table WHERE name='name1' ORDER BY RAND() LIMIT 10) UNION (SELECT * FROM Table WHERE name='name2' ORDER BY RAND() LIMIT 10)

但是，如果我有100个唯一的名称，必须为它们选择10个随机记录，那么此查询将会有点大.

But if I have 100 unique names for which I have to select 10 random records - this query is going to be a bit large.

非常感谢

推荐答案

SQLFiddle演示

select ID,NAME,REPORT_ID from ( select *, @row:=if(name=@name,@row,0)+1 as rn, @name:=name from (select *,RAND() as trand from t) t1, (select @row:=0,@name:='') tm2 order by name,trand ) t2 where rn<=10