如何从表中随机选择唯一的行对? [英] How do I select unique pairs of rows from a table at random?

问题描述

我有两个这样的表:

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中较低ID的人(因为该对的顺序无关紧要).

I want to select pairs of people at random from the people table, and after selecting them I add the randomly select pair to the pairs table. person_a_id always refers to the person with the lower id of the pair (since the order of the pair is not relevant).

问题是我再也不想选择同一对了，所以我需要先检查对表，然后再返回随机选择的对.

The thing is that I never want to select the same pair twice, so I need to check the pairs table before I return my randomly selected pair.

是否可以仅以单个SQL查询以合理有效且优雅的方式执行此操作?

Is it possible to do this using just a single SQL query in a reasonably efficient and elegant manner?

(我正在使用Java Persistence API进行此操作，但希望我能够将所有答案转换为JPA代码)

(I'm doing this using the Java Persistence API, but hopefully I'll be able to translate any answers into JPA code)

推荐答案

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仅返回一对.否则，上限为您需要的对数.

Limit 1 returns just one pair if you are "drawing lots" one at a time. Otherwise, up the limit to however many pairs you need.

上面的查询假设您可以获取

The above query assumes that you can get

1 - 2
2 - 7

，并且配对2 - 7是有效的，因为它不存在，即使再次显示2也是如此.如果您只想让某人出现在only one对中，那么

and that the pairing 2 - 7 is valid since it doesn't exist, even if 2 is featured again. If you only want a person to feature in only one pair ever, then

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，并且 AND 目标表仍然为空，则可以使用此单个查询.请注意，LIMIT 6仅返回3对.

If multiple pairs are to be generated in one single query, AND the destination table is still empty, you could use this single query. Take note that LIMIT 6 returns only 3 pairs.

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:=@p+1 grp
    from (
        select id
        from (select @p:=1) p, people1
        order by rand()
        limit 6
    ) x
) y
group by floor(grp/2)

这篇关于如何从表中随机选择唯一的行对?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！