PostgreSQL组合没有重复
发布时间:2020-12-13 18:07:40 所属栏目:百科 来源:网络整理
导读:如何在postgres中使用字符串或数组并返回一些长度的所有组合? 例如,你有ABC,你想得到2个字符的组合,结果应该是: AB AC 公元前 预先感谢您的帮助. set search_path='tmp';WITH ztab AS (SELECT idx as idx,substring ( 'WTF!' FROM idx FOR 1) as strFROM g
如何在postgres中使用字符串或数组并返回一些长度的所有组合?
例如,你有ABC,你想得到2个字符的组合,结果应该是: AB 预先感谢您的帮助. 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上进行反连接. UPDATE(来自ypercube的提示)似乎OP希望连接字符串.就这样吧:: WITH ztab AS ( SELECT idx as idx,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 :(这是递归的东西…) 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) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |