Oracle SQL排列组合之排列问题
程序员文章站
2022-05-09 19:06:27
...
之前写了一个组合问题,想想还是把排列的情况也考虑下。
表结构 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)
上一篇: 后缀表达式转成中缀表达式
下一篇: MySQL学习笔记(2)
推荐阅读