SQL 两个表之间所有可能的循环组合

Question

给定表:

create table Person( Name varchar(100) )

姓名对所有人都是唯一的

什么 SQL 查询可以生成所有可能的 n!/((n-2)!2!) 循环组合？

假设 Person 的基数总是等于 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

Answer 1

这里有两种解决方法

SELECT  t1.Name + ',' +  t2.Name AS NamesCombination
FROM Person t1
INNER JOIN Person t2
ON t1.Name < t2.Name

或(Oracle 11i R2+)

WITH NamesCombination AS 
( 
    SELECT  1 AS Cntr
            ,Name 
            , CAST(Name AS VARCHAR(MAX))AS NamesCombinations
    FROM Person 
    UNION ALL 
    SELECT  
        nc.Cntr+1
        ,p.Name 
        ,nc.NamesCombinations + ',' +  CAST(p.Name AS VARCHAR(MAX))                      
    FROM Person AS p JOIN NamesCombination nc ON p.Name < nc.Name
    WHERE nc.Cntr < 2  
)
SELECT NamesCombinations
FROM NamesCombination
WHERE Cntr  = 2