如何从表中随机选择唯一的行对？

问题描述：

CREATE TABLE people (
    id INT NOT NULL, 
    PRIMARY KEY (id) 
) 

CREATE TABLE pairs (
    person_a_id INT, 
    person_b_id INT, 
    FOREIGN KEY (person_a_id) REFERENCES people(id), 
    FOREIGN KEY (person_b_id) REFERENCES people(id) 
)

我想从百姓餐桌随机选择对的人，选择它们后，我加入随机选择对的对表。 person_a_id始终是指对中较低的id的人（因为该对的顺序不相关）。

的事情是，我从来没有想选择同一对两次，所以我需要检查对表之前，我回我随机选择的对。

是否有可能做到这一点在一个合理的高效和优雅的方式使用只是一个单一的SQL查询？

（我这样做是使用Java Persistence API，但希望我能翻译任何答案为JPA代码）

这可能是可行的，但它不会很漂亮。 – Matchu 2011-01-27 23:34:31

我不明白这是如何在基于集合的方法中可能的。我可以使用游标来解决它（因此可以在单个存储过程中完成）。最大的障碍是您的选择池必须减少您添加到对表中的每个随机对。 – Matthew 2011-01-27 23:37:02

答

select a.id, b.id 
from people1 a 
inner join people1 b on a.id < b.id 
where not exists (
    select * 
    from pairs1 c 
    where c.person_a_id = a.id 
     and c.person_b_id = b.id) 
order by a.id * rand() 
limit 1;

Limit 1回报只是一对，如果你是“抽签”一次一个。否则，尽可能多的限制，你需要。

上面的查询假设你可以得到

1 - 2 
2 - 7

和配对2 - 7是有效的，因为它不存在，即使2登场亮相。如果你只想要一个人来推荐only one对不断，然后

select a.id, b.id 
from people1 a 
inner join people1 b on a.id < b.id 
where not exists (
    select * 
    from pairs1 c 
    where c.person_a_id in (a.id, b.id)) 
    and not exists (
    select * 
    from pairs1 c 
    where c.person_b_id in (a.id, b.id)) 
order by a.id * rand() 
limit 1;

如果multiple pairs是在一个单一的查询中产生，和目标表仍然是空的，你可以使用这个单号查询。请注意，LIMIT 6只返回3对。

select min(a) a, min(b) b 
from 
(
    select 
     case when mod(@p,2) = 1 then id end a, 
     case when mod(@p,2) = 0 then id end b, 
     @p:[email protected]+1 grp 
    from (
     select id 
     from (select @p:=1) p, people1 
     order by rand() 
     limit 6 
    ) x 
) y 
group by floor(grp/2)

答

这不可能在一个单一的查询来完成基于集合的方法，因为你设置将不知道哪些对插入到对表中。

相反，你应该循环

WHILE EXISTS(SELECT * FROM people 
    WHERE id NOT IN (SELECT person_a_id FROM pairs) 
    AND id NOT IN (SELECT person_b_id FROM pairs)

这将循环，同时还有无与伦比的人。那么你应该从1到表它给你无与伦比的人数的CNT(*)两个随机数...如果你得到了相同的号码，再滚动。（如果你担心这个，随机从集合的两半数字......但那么你就失去了根据您的排序标准的一些随机性）

对那些人。

洗涤，冲洗，重复...... 当您生成相同的随机数两次时，您唯一的“重做”将更容易，因为您只有几个人但仍然只有25％的机会优于1/N^2）

如何从表中随机选择唯一的行对？

相关推荐