PostgreSQL组合，无重复 [英] PostgreSQL combinations without repetitions

问题描述

如何在postgres中使用字符串或数组并返回一定长度的所有组合的函数?

How to make function in postgres that would take string or array and return all combinations of some length?

例如，您有ABC，并且想要获得2个字符的组合，结果应为:

For example you have ABC and you want to get combinations with 2 characters, the result shoul be:

AB 交流电 公元前

AB AC BC

预先感谢您的帮助.

推荐答案

set search_path='tmp';

WITH ztab AS (
SELECT idx as idx
, substring ( 'WTF!' FROM idx FOR 1) as str
FROM generate_series(1, char_length( 'WTF!' )) idx
)
SELECT t1.str, t2.str
FROM ztab t1
JOIN ztab t2 ON t2.idx > t1.idx
        ;

结果:

 str | str 
-----+-----
 W   | T
 W   | F
 W   | !
 T   | F
 T   | !
 F   | !
(6 rows)

不幸的是，我找不到避免双字符串常量的方法. (但是整个内容可以打包到一个函数中)如果没有重复的字符(或者您想阻止它们)，则可以在str上执行反联接，而不是idx.

Unfortunately I cannot find a way to avoid the double string constant. (but the whole thing could be packed into a function) If there are no duplicate characters (or you want to suppres them) you could do the anti-join on the str instead of the idx.

UPDATE(来自ypercube的提示)看来，OP希望将字符串连接在一起.就是这样::

UPDATE (hint from ypercube) It appears that the OP wants the strings to be concatenated. So be it::

WITH ztab AS (
SELECT idx as idx
, substring ( 'WTF!' FROM idx FOR 1) as str
FROM generate_series(1, char_length( 'WTF!' )) idx
)
SELECT t1.str || t2.str AS results
FROM ztab t1
JOIN ztab t2 ON t2.idx > t1.idx
        ;

结果:

 results 
---------
 WT
 WF
 W!
 TF
 T!
 F!
(6 rows)

UPDATE2 :(递归的东西来了...)

UPDATE2: (here comes the recursive thingy...)

WITH RECURSIVE xtab AS (
        WITH no_cte AS (
        SELECT
        1::int AS len
        , idx as idx
        , substring ( 'WTF!' FROM idx FOR 1) as str
        FROM generate_series(1, char_length( 'WTF!' )) idx
        )
        SELECT t0.len as len
                , t0.idx
                , t0.str
        FROM no_cte t0
        UNION SELECT 1+t1.len
                , tc.idx
                , t1.str || tc.str AS str
        FROM xtab t1
        JOIN no_cte tc ON tc.idx > t1.idx
        )
SELECT * FROM xtab
ORDER BY len, str
-- WHERE len=2
        ;

结果3:

 len | idx | str  
-----+-----+------
   1 |   4 | !
   1 |   3 | F
   1 |   2 | T
   1 |   1 | W
   2 |   4 | F!
   2 |   4 | T!
   2 |   3 | TF
   2 |   4 | W!
   2 |   3 | WF
   2 |   2 | WT
   3 |   4 | TF!
   3 |   4 | WF!
   3 |   4 | WT!
   3 |   3 | WTF
   4 |   4 | WTF!
(15 rows)

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