sql实现排列组合

employe  表中的数据
EMPNO ENAME JOB DEPTNO SAL
101 实施一部员工1 部门领导 10 1205.775
102 实施一部员工2 普通员工 10 1341.174
103 实施一部员工3 普通员工 10 1195.74
104 实施一部员工4 普通员工 10 1529.176
105 实施一部员工5 普通员工 10 1901.084
106 实施一部员工6 普通员工 10 1240.569
107 实施一部员工7 普通员工 10 1543.701
108 实施一部员工8 普通员工 10 1940.666
109 实施一部员工9 普通员工 10 1099.344
201 实施二部员工1 部门领导 20 1168.395
202 实施二部员工2 普通员工 20 1279.73
203 实施二部员工3 普通员工 20 1629.773
204 实施二部员工4 普通员工 20 1434.098
205 实施二部员工5 普通员工 20 1200.323
206 实施二部员工6 普通员工 20 1301.527
207 实施二部员工7 普通员工 20 1845.861
208 实施二部员工8 普通员工 20 1339.908
209 实施二部员工9 普通员工 20 1319.262
301 BBBBB 部门领导 30 10059342
302 BBBBB 普通员工 30 13510598
303 BBBBB 普通员工 30 14124001
304 BBBBB 普通员工 30 10281531
305 BBBBB 普通员工 30 15048031
306 BBBBB 普通员工 30 10574133
307 BBBBB 普通员工 30 15250713
308 BBBBB 普通员工 30 13588951
309 BBBBB 普通员工 30 18616796

-----以下两条sql是按照deptno的维度得到empno的排列组合

----这条sql就不具体介绍了
with table_a1 as (select * from employe e where e.deptno=10),
table_a2 as (select * from employe e where e.deptno=20),
table_a3 as (select * from employe e where e.deptno=30)
select a1.empno,a2.empno,a3.empno from table_a1 a1,table_a2 a2 ,table_a3 a3
where a1.empno <>a2.empno and a2.empno <>a3.empno and a1.empno <>a3.empno
 
-----这条sql使用到了  connect by 语法
SELECT  ccc,
   rtrim(regexp_substr( ccc || ',', '.*?' || ',', 1, 1), ',') AS c1,
   rtrim(regexp_substr( ccc || ',', '.*?' || ',', 1, 2), ',') AS c2,
   rtrim(regexp_substr( ccc || ',', '.*?' || ',', 1, 3), ',') AS c3
   FROM (select level, ltrim(sys_connect_by_path(empno, ','),',') ccc
  from employe where level=3 connect by prior ascii(deptno) < ascii(deptno) and level<=4)
ORDER BY 1;

Oracle “CONNECT BY”是层次查询子句，一般用于树状或者层次结果集的查询。其语法是：
 [ START WITH condition ]  CONNECT BY [ NOCYCLE ] condition
说明：
1. START WITH：告诉系统以哪个节点作为根结点开始查找并构造结果集，该节点即为返回记录中的最高节点。
2. 当分层查询中存在上下层互为父子节点的情况时，会返回ORA-01436错误。此时，需要在connect by后面加上NOCYCLE关键字。同时，可用connect_by_iscycle伪列定位出存在互为父子循环的具体节点。 connect_by_iscycle必须要跟关键字NOCYCLE结合起来使用

这条sql中使用到 sys_connect_by_path 的一种灵活用法，以下说明下connect的几个函数以及level伪列 具体的就不一一举例说明了
说明：
1. CONNECT_BY_ROOT 返回当前节点的最顶端节点
2. CONNECT_BY_ISLEAF 判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点
3. LEVEL 伪列表示节点深度
4. SYS_CONNECT_BY_PATH函数显示详细路径，并用符号分隔

我们再继续介绍两个正则表达式函数regexp_replace和regexp_substr。

regexp_replace函数和regexp_substr函数的功能类似与字符函数replace和substr,只是这里的应用更加灵活，可以一下子匹配一系列的结果，而原来的函数只能一下子匹配一个固定的字符换。

具体来讲：
regexp_replace的意义是找到于给定模式匹配的字符串并用其他的字符串来替代。
其原型是：regexp_replace(x,pattern[,replace_string[,start[,occurence[match_option]]]])
每个参数的意思分别是：
x 待匹配的函数
pattern 正则表达式元字符构成的匹配模式
replace_string 替换字符串
start 开始位置
occurence　匹配次数
match_option　匹配参数，这里的匹配参数和regexp_like是完全一样的，可参考前面的一篇文章。

举例来讲：
select regexp_replace(’hello everybody,may I have your attention please?’,'b[[:alpha:]]{3}’,'one’) from dual将会返回结果：
hello everyone,may I have your attention please?

而regexp_substr函数的意义找出与给定模式匹配的字符串并返回，
其原型是：regexp_substr(x,pattern[,start[,occurence[match_option]]])
这里各参数的意义与前面的函数regexp_replace的含义是一样做的。
regexp_substr的例子如下：
select regexp_substr(’I love oracle very much’,'o[[:alpha:]]{5}’) from dual;
这里将会匹配出结果：
oracle来。这也是这个函数的返回结果。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23754390/viewspace-683759/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23754390/viewspace-683759/