Oracle高级使用

递归

https://zhuanlan.zhihu.com/p/57896069 https://www.jb51.net/article/170486.htm https://blog.csdn.net/pdsuxueyuan/article/details/111467484 CONNECT BY nocycle  防止id和pid一致

select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid

分组开窗函数

ow_number()over(partition by col1 order by col2)
表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。

字符串拆分

https://blog.csdn.net/iteye_4537/article/details/82407788?spm=1001.2101.3001.6661.1&utm_medium=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-1-82407788-blog-106766501.pc_relevant_blogantidownloadv1&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-1-82407788-blog-106766501.pc_relevant_blogantidownloadv1&utm_relevant_index=1 function REGEXP_SUBSTR(string, pattern, position, occurrence, modifier) string ：需要进行正则处理的字符串pattern ：进行匹配的正则表达式position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）occurrence ：标识第几个匹配组，默认为1modifier ：模式(‘i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c')

SELECT REGEXP_SUBSTR('17,20,23', '[^,]+', 1, LEVEL, 'i') AS STR
  FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT('17,20,23', ',') +1

SELECT REGEXP_SUBSTR('123,,,ABC,!@#,,,', '[^,]+', 1, LEVEL) 
FROM DUAL 
CONNECT BY REGEXP_SUBSTR('123,,,ABC,!@#,,,', '[^,]+', 1, LEVEL) IS NOT NULL;

123
ABC
!@#

数据表字段拆分直接执行会重复故加上DISTINCT

SELECT DISTINCT REGEXP_SUBSTR(USER_BU, '[^,]+', 1, LEVEL, 'i') AS STR ,USER_NAME
  FROM BUORDER_PERMISSIONS WHERE USER_NAME ='Q'
CONNECT BY LEVEL <= REGEXP_COUNT(USER_BU, ',') +1