Oracle SQL排列组合之排列问题

之前写了一个组合问题，想想还是把排列的情况也考虑下。

表结构 t_data

期望结果

一共3列数据，比如选取2列时，（C1C2）（C2C1）这两个排列值为2，因为只有2行满足c1、c2都不为空（第1、3行）

总体思路：

构造3列的排列，然后和实际数据匹配

第一步：构造3列的排列

SQL> select regexp_substr(csv.csvdata, '[^,]+', 1, level) chr
  2    from (select '1,2,3' as csvdata from dual) csv
  3  connect by level <= 3 
  4  /

CHR
----------
1
2
3


SQL> create table t_Permutation as
  2  select regexp_substr(csv.csvdata, '[^,]+', 1, level) chr
  3    from (select '1,2,3' as csvdata from dual) csv
  4  connect by level <= 3
  5  /

Table created.

SQL> select * from t_Permutation;

CHR
----------
1
2
3

SQL> select sys_connect_by_path(chr, ' ') path
  2    from t_Permutation
  3  connect by nocycle prior chr <> chr
  4  /

 PATH
 -------------
 1 2
 1 3 2
 1
 3 1 2
 2 1 3
 3
 3 1
 2
 1 2 3
 2 1
 2 3 1
 3 2
 3 2 1
 1 3
 2 3

15 rows selected.
至此，元素1、2、3的排列构造完成

第二步：获得表中数据列的组合情况

SQL> create table t_data (          
  2  id varchar2(1),         
  3  c1 int,         
  4  c2 int,         
  5  c3 int,         
  6  c4 varchar2(1)          
  7  )
  8  /

Table created.

SQL> insert into t_data values ('a',1,1,null,'Y'); 
1 row created.
SQL> insert into t_data values ('b',null,1,1,'Y'); 
1 row created.
SQL> insert into t_data values ('c',1,1,1,'Y'); 
1 row created.
SQL> insert into t_data values ('d',1,null,null,'Y'); 
1 row created.
SQL> commit;   
Commit complete.

SQL> select * from t_data;
I        C1         C2         C3          C4
-------  -------    -------    -------     -------
a        1          1                      Y
b                   1          1           Y
c        1          1          1           Y
d        1                                 Y

SQL> select nvl2(c1, '1', null) || nvl2(c2, '2', null) || nvl2(c3, '3', null) col_not_null
  2    from t_data
  3  /

COL
---
12
23
123
1

至此表中非空列数据的组合数据加工完成，剩下的工作主要是匹配，如怎样将 组合数据12 匹配以下排列（1,2,12,21）

第三步：用构造出来的排列和实际的组合正则匹配即可，其中将排列的结果构造成符合后面正则匹配的列表形式，如[12],可以方便匹配组合数据。

SQL> col path for a6
SQL> select '[' || regexp_replace(path, ' ') || ']' path,
  2         regexp_count(path, '[0-9]') len
  3    from (select sys_connect_by_path(chr, ' ') path
  4            from t_Permutation
  5          connect by nocycle prior chr <> chr) t
  6   order by len, path
  7  /

PATH                LEN
--------                       ----------
[1]               1
[2]               1
[3]               1
[12]              2
[13]              2
[21]              2
[23]              2
[31]              2
[32]              2
[123]              3
[132]              3
[213]              3
[231]              3
[312]              3
[321]              3

15 rows selected.

第四步：获取最终结果

SQL> select path, count(1) cnt
  2    from (select *
  3            from (select '[' || regexp_replace(path, ' ') || ']' path,
  4                         regexp_count(path, '[0-9]') len
  5                    from (select sys_connect_by_path(chr, ' ') path
  6                            from t_Permutation
  7                          connect by nocycle prior chr <> chr)) a,
  8                 (select nvl2(c1, '1', null) || nvl2(c2, '2', null) ||
  9                         nvl2(c3, '3', null) col_not_null
 10                    from t_data) b
 11           where regexp_instr(b.col_not_null, a.path, 1, a.len) > 0)
 12   group by path
 13  /

PATH                CNT
----------          ----------
[1]                 3
[321]               1
[312]               1
[2]                 3
[13]                1
[132]               1
[213]               1
[231]               1
[23]                2
[3]                 2
[32]                2
[12]                2
[31]                1
[123]               1
[21]                2

15 rows selected.

总结：解释下匹配的逻辑，如表中组合数据12是可以匹配以下排列（1,2,12,21），结合排列的长度，使用正则表达式,如

regexp_instr(b.col_not_null, a.path, 1, a.len) > 0
regexp_instr('12','[1]',1,1)
regexp_instr('12','[2]',1,1)
regexp_instr('12','[12]',1,2)
regexp_instr('12','[21]',1,2)

不管排列和组合，元素都不会重复，而 regexp_instr('12','[12]',1,2) 和 regexp_instr('12','[21]',1,2) 是等价的，所以组合数据12，可以匹配排列（12,21）