每个指定的组选择n个随机行 [英] select n random rows per specified group

问题描述

我正在努力寻找以下问题的最佳解决方案. 假设我有一个像这样的表"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

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