SQL两个表之间的所有可能的循环组合 [英] SQL All Possible Round Robin Combinations between two Tables

问题描述

给定表:

create table Person( Name varchar(100) )

所有人的名字都是唯一的

哪个SQL查询可以生成所有可能的n！/(((n-2)！2！)

假定人的基数始终等于4

示例人物= {'Anna'，'Jerome'，'Patrick'，'Michael')

输出:

Anna, Jerome
Anna, Patrick
Anna, Michael
Jerome, Patrick
Jerome, Michael
Patrick, Michael

任何帮助将不胜感激.谢谢！

这是我的答案(我使用了oracle SQL):

select P1.NAME PERSON1, P2.NAME PERSON2
  from (select rownum RNUM, NAME
        from PERSON) P1,
       (select rownum RNUM, NAME
          from PERSON) P2
 where P1.RNUM < P2.RNUM

解决方案

select P1.NAME PERSON1, P2.NAME PERSON2
  from (select rownum RNUM, NAME
        from PERSON) P1,
       (select rownum RNUM, NAME
          from PERSON) P2
 where P1.RNUM < P2.RNUM

given table:

create table Person( Name varchar(100) )

where Name is unique for all Persons

What SQL query can generate all possible n!/((n-2)!2!) round robin combinations?

It is assumed that the cardinality of Person is ALWAYS equal to 4

Example Person = {'Anna','Jerome','Patrick','Michael')

Output:

Anna, Jerome
Anna, Patrick
Anna, Michael
Jerome, Patrick
Jerome, Michael
Patrick, Michael

Any help would be appreciated. Thanks!

Here's my answer (I used oracle SQL):

select P1.NAME PERSON1, P2.NAME PERSON2
  from (select rownum RNUM, NAME
        from PERSON) P1,
       (select rownum RNUM, NAME
          from PERSON) P2
 where P1.RNUM < P2.RNUM

解决方案

select P1.NAME PERSON1, P2.NAME PERSON2
  from (select rownum RNUM, NAME
        from PERSON) P1,
       (select rownum RNUM, NAME
          from PERSON) P2
 where P1.RNUM < P2.RNUM

这篇关于SQL两个表之间的所有可能的循环组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！