在组中选择随机记录 [英] Select random record within groups sqlite

问题描述

我似乎无法解决这个问题.我在SQlite中有一个表，我需要从中为每个组选择一个random()记录.因此，考虑一个表，例如:

I can't seem to get my head around this. I have a single table in SQlite, from which I need to select a random() record for EACH group. So, considering a table such as:

id         link       chunk

2           a           me1

3           b           me1

4           c           me1

5           d           you2

6           e           you2

7           f           you2

我需要sql，它将为每个块返回一个随机链接值.所以有一次我运行它会给出:

I need sql that will return a random link value for each chunk. So one time I run it would give:

me1    |   a
you2   |   f

下次可能

me1    |   c
you2   |   d

我知道已经回答了类似的问题，但我找不到适用于此的问题的推论.

I know similar questions have been answered but I'm not finding a derivation of one that applies here.

更新:

坚果，跟进问题:所以现在我需要排除新字段"qcinfo"设置为"Y"的行.

Nuts, follow up question: so now I need to EXCLUDE rows where a new field "qcinfo" is set to 'Y'.

这当然会在qcinfo ='Y'的情况下在随机ID达到1时隐藏行，这是错误的.我需要将该行排除在大块之外，但如果有记录具有qcinfo<>'Y'，则仍会为该大块生成随机记录.

This, of course, hides rows whenever the random ID hits one where qcinfo = 'Y', which is wrong. I need to exclude the row from being considered in the chunk, but still generate a random record for the chunk if any records have qcinfo <> 'Y'.

select  t.chunk ,t.id, t.qcinfo, t.link from  table1
inner join
        (
        select chunk ,cast(min(id)+abs(random() % (max(id)-min(id)))as int) AS random_id
        from table1
        group by chunk
        ) sq
on      t.chunk = sq.chunk
and     t.id = sq.random_id
where qcinfo <> 'Y'

推荐答案

有点黑，但它可以工作...请参见sql小提琴

A bit hackish, but it works... See sql fiddle http://sqlfiddle.com/#!2/81e75/7

select  t.chunk
        ,t.link
from    table1 t
inner join
        (
        select chunk
               ,FLOOR(min(id) + RAND() * (max(id)-min(id))) AS random_id
        from    table1
        group by chunk
        ) sq
on      t.chunk = sq.chunk
and     t.id = sq.random_id

对不起，我以为你说的是​​MySQL. 这是SQLite的小提琴和代码

Sorry, I thought that you said MySQL. Here is the fiddle and the code for SQLite

http://sqlfiddle.com/#!5/81e75/12

select  t.chunk
        ,t.link
from    table1 t
inner join
        (
        select chunk
               ,cast(min(id)+abs(random() % (max(id)-min(id)))as int) AS random_id
        from    table1
        group by chunk
        ) sq
on      t.chunk = sq.chunk
and     t.id = sq.random_id

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