代码之家 › 专栏 › 技术社区 › Christian Hagelid

如何选择一组随机记录,其中一列是唯一的?

random sql

Christian Hagelid · 技术社区 · 16 年前

今天,我一直在努力满足这个SQL查询要求,我想知道是否有人能帮我。

我有一张体育问题表。其中一列是与该问题相关的团队。我的要求是返回一组随机问题,其中团队是唯一的。

假设我们有下表,想要5个问题:

Question        Answer        Team
-----------------------------------
question 1      answer 1      team A
question 2      answer 2      team B
question 3      answer 3      team B
question 4      answer 3      team D
question 5      answer 3      team A
question 6      answer 3      team C
question 7      answer 3      team F
question 8      answer 3      team C
question 9      answer 3      team G
question 10     answer 3      team D

有效结果将返回:

question 1      answer 1      team A
question 2      answer 2      team B
question 4      answer 3      team D
question 6      answer 3      team C
question 7      answer 3      team F

我认为,通过巧妙地使用Distinct和Take,应该可以将其作为一个干净的SQL语句来实现,但我还没有做到这一点。

到目前为止,最好的解决方案是 Mladen Prajdic 。我刚刚稍微更新了它,以改善它的随机性:

SELECT TOP 10 * 
FROM    (SELECT ROW_NUMBER() OVER(PARTITION BY Team ORDER BY Team, NEWID()) AS RN, *
    FROM Question
    ) teams
WHERE   RN = 2
ORDER BY NEWID()

3 回复 | 直到 8 年前

Mladen Prajdic 16 年前

对于sql 2005,您可以这样做:

select top 5 * 
from    (
            select ROW_NUMBER() over(partition by team order by team) as RN, *
            from @t 
        ) t
where RN = 1
order by NEWID()

William 16 年前

在oracle中,这应该能满足你的需要;显然,对于不同的数据库,您需要使用它们的随机数源。也许有更好的办法;希望其他人能向我们指出:p

select question, answer, team
from
(
select question, answer, team, r
from
(
select 
    question, 
    answer, 
    team,
    rank() over (partition by team order by dbms_random.value) r 
from questions
)
where r = 1
order by dbms_random.value
) where rownum<=5;

测试代码:

create table questions(question varchar2(16), answer varchar2(16), team varchar2(16));

insert into questions(question, answer, team)
values ('question 1',      'answer 1',      'team A');

insert into questions(question, answer, team)
values ('question 2',      'answer 2',      'team B');

insert into questions(question, answer, team)
values ('question 3',      'answer 3',      'team B');

insert into questions(question, answer, team)
values ('question 4',      'answer 3',      'team D');

insert into questions(question, answer, team)
values ('question 5',      'answer 3',      'team A');

insert into questions(question, answer, team)
values ('question 6',      'answer 3',      'team C');

insert into questions(question, answer, team)
values ('question 7',      'answer 3',      'team F');

insert into questions(question, answer, team)
values ('question 8',      'answer 3',      'team C');

insert into questions(question, answer, team)
values ('question 9',      'answer 3',      'team G');

insert into questions(question, answer, team)
values ('question 10',    'answer 3',      'team D');

commit;

pilif 16 年前

在PostgreSQL(它有明确的on)中,我可能会做这样的事情:

select distinct on (Team) Question, Answer, Team from test order by Team, random() limit 5;

刚刚测试过。似乎有效。