PostgreSQL的组合，从不同的排列 [英] PostgreSQL combinations as distinct from permutations

问题描述

无法弄清楚如何使阵列组合，而无需重复。

 输入n个变量组成的数组LIKE'{A，B，C，D，E}
   变量A始终是单个阵列LIKE'{A}或{D，C}或{D，A，B}等。   我需要的是输入，A（I）相结合 例：
     1，A ='{B}' - ＆GT; ATT ='{B，A，B，C，B，D，B，E}
     2. A ='{B，C}' - ＆GT; ATT ='{B，C，A，B，C，D，B，C，E}
     3. A ='{B，C，A}' - ＆GT; ATT ='{B，C，A，D，B，C，A，E}
     4. A ='{B，C，A，D}' - ＆GT; ATT ='{B，C，A，D，E}
 

据我有这样的：

  WITH A（I）AS（SELECT * FROM UNNEST（ARRAY ['A，B']））
     B（j）条AS（SELECT * FROM UNNEST（ARRAY ['A'，'B'，'C'，'D'）），     CTE AS（SELECT A.I ||'，'|| B.j
              从
              CROSS JOIN B
            ）
     选择Array（SELECT * FROM CTE）INTO ATT;
 

但它使口是心非：

 {A，B，A，A，B，B，A，B，C，A，B，D}
 

解决方案

  WITH RECURSIVE     A（I）AS（SELECT * FROM UNNEST（ARRAY ['A，B']））
     B（j）条AS（SELECT * FROM UNNEST（ARRAY ['A'，'B'，'C'，'D'）），     CTE AS（选择j可AS组合，J，1 AS克拉
               从B
             UNION ALL
               SELECT cte.combo ||'，'|| B.j，B.j，CT + 1
               FROM CTE，B
               WHERE CT＆LT; = 4
                和位置（B.j在cte.combo）= 0
            ）
    ，COM AS（SELECT A.I ||'，'|| B.j中巴
               从
               CROSS JOIN B）     选择Array（选择CMB对CTE，com其中cte.combo = com.cmb）
 

Can't figure out how to make combinations of arrays without repetitions.

   INPUT is an array of n variables LIKE '{A,B,C,D,E}'
   Variable A is always single array LIKE '{"A"}' or '{"D,C"}' or '{"D,A,B"}' etc..

   What I need is to combine INPUT with A(i)

 EXAMPLE:
     1. A = '{"B"}'       --> att='{"B,A","B,C","B,D","B,E"}'
     2. A = '{"B,C"}'     --> att='{"B,C,A","B,C,D","B,C,E"}'
     3. A = '{"B,C,A"}'   --> att='{"B,C,A,D","B,C,A,E"}'
     4. A = '{"B,C,A,D"}' --> att='{"B,C,A,D,E"}'

As far I have this:

WITH A(i) AS (SELECT * FROM unnest(ARRAY['A,B'])),
     B(j) AS (SELECT * FROM unnest(ARRAY['A','B','C','D'])),

     cte AS ( SELECT A.i ||','|| B.j
              FROM A
              CROSS JOIN   B 
            )
     SELECT ARRAY ( SELECT * FROM cte) INTO att;

But it makes duplicity:

"{"A,B,A","A,B,B","A,B,C","A,B,D"}"

解决方案

WITH  RECURSIVE 

     A(i) AS (SELECT * FROM unnest(ARRAY['A,B'])),
     B(j) AS (SELECT * FROM unnest(ARRAY['A','B','C','D'])),

     cte AS (  SELECT j AS combo, j, 1 AS ct 
               FROM B 
             UNION ALL 
               SELECT cte.combo ||','||B.j, B.j, ct + 1 
               FROM cte, B
               WHERE ct <= 4
                AND position(B.j in cte.combo) = 0
            )
    , com AS ( SELECT A.i ||','|| B.j AS cmb
               FROM A
               CROSS JOIN B)

     SELECT ARRAY(SELECT cmb FROM cte,com where cte.combo = com.cmb)

这篇关于PostgreSQL的组合，从不同的排列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！