浅析REGEXP_SUBSTR,PRIOR,CONNECT BY
业务场景https://img2024.cnblogs.com/blog/2735096/202411/2735096-20241125170913141-1589307063.png
teacher表中的tech_class字段存储的是每个老师所传授的课程，课程之间以英文逗号分隔。现在要用语句统计每个课程对应的教师数量。语句及效果如下：
https://img2024.cnblogs.com/blog/2735096/202411/2735096-20241125171007908-2056522754.png
语句着实很简朴，各种博客大概gpt都有不错且可行的解决方案，我们重要来明白下这段语句的执行原理，更好的学习。
part1 REGEXP_SUBSTR
关于REGEXP_SUBSTR的官方文档
详细语法这里不再赘叙，我们从单个例子入手看看效果：
https://img2024.cnblogs.com/blog/2735096/202411/2735096-20241125172654543-343182469.png
https://img2024.cnblogs.com/blog/2735096/202411/2735096-20241125172706526-2069334154.png
REGEXP_SUBSTR可以将字段字符串根据所给正则表达式匹配并拆分（注意不是分割，但效果上等同于分割）。
最后一个参数代表要取出第几个匹配的结果：
https://img2024.cnblogs.com/blog/2735096/202411/2735096-20241125172738238-1001478674.png
那为什么这里要利用LEVEL？LEVEL是什么？
关于LEVEL的官方解释 详细如图：
https://img2024.cnblogs.com/blog/2735096/202411/2735096-20241126090745647-950290573.png
利用之前要注意，官方文档里有句话：
To define a hierarchical relationship in a query, you must use the CONNECT BY clause.
以是关于connect by，你可以先往后看。
利用LEVEL后的效果：
https://img2024.cnblogs.com/blog/2735096/202411/2735096-20241126090911618-1686259575.png
LEVEL是一个在CONNECT BY子句中利用的伪列，它代表当前递归层次的级别。在每次递归调用中，LEVEL的值会增加1。在这个例子中，LEVEL的值会从1开始，一直到tech_class中逗号分隔的子串的数量——3。
为什么如许会有81条？我们的预期结果着实是3条。让我们继续探究......
https://img2024.cnblogs.com/blog/2735096/202411/2735096-20241126091007827-790375514.png
https://img2024.cnblogs.com/blog/2735096/202411/2735096-20241126091027645-998159748.png
https://img2024.cnblogs.com/blog/2735096/202411/2735096-20241126091042078-654505969.png
Football是字段里的第一个值，只有1条；Basketball是字段里的第二个值，有10条；PingPang是字段里的第三个值，有70条！貌似越往后数据重复越多，而且次数增长的可怕，但很难发现出有什么规律。检索后基本确定出现重复数据是因为在递归过程中，regexp_substr函数没有正确移动到下一个匹配项，而是重复移动到了Basketball大概PingPang，至于它底层是什么重复移动的，额我也没搞明白....。
对此我们必要添加prior确保每次递归时都能正确提取。
part3 prior
关于prior的简朴介绍
connect by中加prior可以限定父子的对应关系，限定递归路径。这里对同条记录进行递归：
https://img2024.cnblogs.com/blog/2735096/202411/2735096-20241126091421194-902967676.png
加sys_guid()是为了包管层次查询，存在循环时，不出现无限递归。它为每行生成一个唯一标识，从而制止无限循环。
Part3 connect by
CONNECT BY的官方文档--分级查询
connect by经常结合prior一起实现父级查询。因此connect by LEVEL prior一样平常都一起出现。
附
最后再次附上针对原始的业务需求的完备的语句及输出：
selectregexp_substr(tech_class, '[^,]+', 1, LEVEL) as class_name,tech_namefrom teacherCONNECT BY LEVEL
页:
[1]