oracle connect by 递归查询用法

曾经终败给现在 2021-09-26 14:38 487阅读 0赞

### 基本语法 ###

*  start with ：设置起点，省略后默认以全部行为起点。
 *  connect by \[condition\] ：与一般的条件一样作用于当前列，但是在满足条件后，会以全部列作为下一层级递归（没有其他条件的话）。
 *  prior ： 表示上一层级的标识符。经常用来对下一层级的数据进行限制。不可以接伪列。
 *  level ：伪列，表示当前深度。
 *  connect\_by\_root() ：显示根节点列。经常用来分组。
 *  connect\_by\_isleaf ：1是叶子节点，0不是叶子节点。在制作树状表格时必用关键字。
 *  sys\_connect\_by\_path() ：将递归过程中的列进行拼接。
 *  nocycle , connect\_by\_iscycle : 在有循环结构的查询中使用。
 *  siblings : 保留树状结构，对兄弟节点进行排序

举几个例子。  
先建一张测试表。

create table nayi_180328_connect_test( dept_id varchar2(50), parent_id varchar2(50), dept_name varchar2(100), dept_rank varchar2(2000), val number); insert into nayi_180328_connect_test select 'root', '', '全国', '', 0 from dual union all select 'root_1', 'root', '北京市', '', 2000 from dual union all select 'ln_root', 'root', '辽宁省', '', 200 from dual union all select 'ln_ys', 'ln_root', '辽宁省沈阳市', '', 1000 from dual union all select 'ln_sy_hp', 'ln_ys', '辽宁省沈阳和平区', '', 500 from dual union all select 'ln_ys_dd', 'ln_ys', '辽宁省沈阳大东区', '', 600 from dual union all select 'jl_root', 'root', '吉林省', '', 0 from dual union all select 'jl_jl', 'jl_root', '吉林省吉林市', '', 200 from dual union all select 'jl_cc', 'jl_root', '吉林省长春市', '', 500 from dual ; commit;

数据：

<table> 
 <thead> 
  <tr> 
   <th>DEPT_ID</th> 
   <th>PARENT_ID</th> 
   <th>DEPT_NAME</th> 
   <th>DEPT_RANK</th> 
   <th>VAL</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>root</td> 
   <td></td> 
   <td>全国</td> 
   <td></td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>root_1</td> 
   <td>root</td> 
   <td>北京市</td> 
   <td></td> 
   <td>2000</td> 
  </tr> 
  <tr> 
   <td>ln_root</td> 
   <td>root</td> 
   <td>辽宁省</td> 
   <td></td> 
   <td>200</td> 
  </tr> 
  <tr> 
   <td>ln_ys</td> 
   <td>ln_root</td> 
   <td>辽宁省沈阳市</td> 
   <td></td> 
   <td>1000</td> 
  </tr> 
  <tr> 
   <td>ln_sy_hp</td> 
   <td>ln_ys</td> 
   <td>辽宁省沈阳和平区</td> 
   <td></td> 
   <td>500</td> 
  </tr> 
  <tr> 
   <td>ln_ys_dd</td> 
   <td>ln_ys</td> 
   <td>辽宁省沈阳大东区</td> 
   <td></td> 
   <td>600</td> 
  </tr> 
  <tr> 
   <td>jl_root</td> 
   <td>root</td> 
   <td>吉林省</td> 
   <td></td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>jl_jl</td> 
   <td>jl_root</td> 
   <td>吉林省吉林市</td> 
   <td></td> 
   <td>200</td> 
  </tr> 
  <tr> 
   <td>jl_cc</td> 
   <td>jl_root</td> 
   <td>吉林省长春市</td> 
   <td></td> 
   <td>500</td> 
  </tr> 
 </tbody> 
</table>

#### 无prior ####

select t.*, level, CONNECT_BY_ROOT(num) root, lpad('---', level * 3 - 1, '-') || '>' || num num, connect_by_isleaf isleaf from (select 7 as num from dual union all select 8 as num from dual ) t connect by level <= 3;

<table> 
 <thead> 
  <tr> 
   <th>NUM</th> 
   <th>LEVEL</th> 
   <th>ROOT</th> 
   <th align="left">NUM_LEVEL</th> 
   <th>ISLEAF</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>7</td> 
   <td>1</td> 
   <td>7</td> 
   <td align="left">–&gt;7</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>2</td> 
   <td>7</td> 
   <td align="left">-----&gt;7</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>3</td> 
   <td>7</td> 
   <td align="left">--------&gt;7</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>3</td> 
   <td>7</td> 
   <td align="left">--------&gt;8</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>2</td> 
   <td>7</td> 
   <td align="left">-----&gt;8</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>3</td> 
   <td>7</td> 
   <td align="left">--------&gt;7</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>3</td> 
   <td>7</td> 
   <td align="left">--------&gt;8</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>1</td> 
   <td>8</td> 
   <td align="left">–&gt;8</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>2</td> 
   <td>8</td> 
   <td align="left">-----&gt;7</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>3</td> 
   <td>8</td> 
   <td align="left">--------&gt;7</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>3</td> 
   <td>8</td> 
   <td align="left">--------&gt;8</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>2</td> 
   <td>8</td> 
   <td align="left">-----&gt;8</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>3</td> 
   <td>8</td> 
   <td align="left">--------&gt;7</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>3</td> 
   <td>8</td> 
   <td align="left">--------&gt;8</td> 
   <td>1</td> 
  </tr> 
 </tbody> 
</table>

可以很明显的看出当满足当前行条件（level ❤️）时，下一层为全部行（两行）。这是对于每一个“当前行”都生效的。  
它也相当于对每一层级做层级数的笛卡尔积。即与下面的语句等效。

with tab as ( select 7 as num from dual union all select 8 as num from dual) select t1.*, 1 l_level from tab t1 union all select t1.*, 2 from tab t1, tab t2 union all select t1.*, 3 from tab t1, tab t2, tab t3;

当初始数据为一行时最为常用。此时会生成层级数目的行，再配合level生成数据。  
例：

select level * 2 "生成线性数字", to_char(add_months(sysdate, -level + 1), 'yyyymm') "当前月份前的n个月份", regexp_substr('/root/ln_root/ln_ys/ln_sy_hp', '[^/]+', 1, level) "配合正则表达式分隔字符串" from dual connect by level <= 5;

<table> 
 <thead> 
  <tr> 
   <th>生成线性数字</th> 
   <th>当前月份前的n个月份</th> 
   <th>配合正则表达式分隔字符串</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>201804</td> 
   <td>root</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>201803</td> 
   <td>ln_root</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>201802</td> 
   <td>ln_ys</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>201801</td> 
   <td>ln_sy_hp</td> 
  </tr> 
  <tr> 
   <td>10</td> 
   <td>201712</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

#### 有prior ####

最简单的树状查询

select t1.* from nayi_180328_connect_test t1 where 1 = 1 start with t1.dept_id = 'root' connect by prior t1.dept_id = t1.parent_id;

<table> 
 <thead> 
  <tr> 
   <th>DEPT_ID</th> 
   <th>PARENT_ID</th> 
   <th>DEPT_NAME</th> 
   <th>DEPT_RANK</th> 
   <th>VAL</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>root</td> 
   <td></td> 
   <td>全国</td> 
   <td></td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>jl_root</td> 
   <td>root</td> 
   <td>吉林省</td> 
   <td></td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>jl_cc</td> 
   <td>jl_root</td> 
   <td>吉林省长春市</td> 
   <td></td> 
   <td>500</td> 
  </tr> 
  <tr> 
   <td>jl_jl</td> 
   <td>jl_root</td> 
   <td>吉林省吉林市</td> 
   <td></td> 
   <td>200</td> 
  </tr> 
  <tr> 
   <td>ln_root</td> 
   <td>root</td> 
   <td>辽宁省</td> 
   <td></td> 
   <td>200</td> 
  </tr> 
  <tr> 
   <td>ln_ys</td> 
   <td>ln_root</td> 
   <td>辽宁省沈阳市</td> 
   <td></td> 
   <td>1000</td> 
  </tr> 
  <tr> 
   <td>ln_sy_hp</td> 
   <td>ln_ys</td> 
   <td>辽宁省沈阳和平区</td> 
   <td></td> 
   <td>500</td> 
  </tr> 
  <tr> 
   <td>ln_ys_dd</td> 
   <td>ln_ys</td> 
   <td>辽宁省沈阳大东区</td> 
   <td></td> 
   <td>600</td> 
  </tr> 
  <tr> 
   <td>root_1</td> 
   <td>root</td> 
   <td>北京市</td> 
   <td></td> 
   <td>2000</td> 
  </tr> 
 </tbody> 
</table>

prior可以有多个  
比如这样

select * from nayi_180328_connect_test t1 where 1 = 1 start with t1.dept_id = 'root' connect by prior t1.dept_id = t1.parent_id and prior t1.dept_id != prior t1.dept_name

表示当前行需满足上一层级的id与name不可相等，显然与第一句结果集相等。  
或者这样

select * from nayi_180328_connect_test t1 where 1 = 1 start with t1.dept_id = 'root' connect by prior t1.dept_id = t1.parent_id and prior t1.dept_id || prior t1.parent_id != 'root'

表示上一行的id与name拼接后不能等于root，它只能查出起始行。

具体条件要根据实际需要去调整。

\#\#\#\#　查询语句中的prior。  
同样查出上一层级的相应数据

select t1.*, prior dept_id from nayi_180328_connect_test t1 where 1 = 1 start with t1.dept_id = 'root' connect by prior t1.dept_id = t1.parent_id ;

<table> 
 <thead> 
  <tr> 
   <th>DEPT_ID</th> 
   <th>PARENT_ID</th> 
   <th>DEPT_NAME</th> 
   <th>DEPT_RANK</th> 
   <th>VAL</th> 
   <th>PRIORDEPT_ID</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>root</td> 
   <td></td> 
   <td>全国</td> 
   <td></td> 
   <td>0</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>jl_root</td> 
   <td>root</td> 
   <td>吉林省</td> 
   <td></td> 
   <td>0</td> 
   <td>root</td> 
  </tr> 
  <tr> 
   <td>jl_cc</td> 
   <td>jl_root</td> 
   <td>吉林省长春市</td> 
   <td></td> 
   <td>500</td> 
   <td>jl_root</td> 
  </tr> 
  <tr> 
   <td>jl_jl</td> 
   <td>jl_root</td> 
   <td>吉林省吉林市</td> 
   <td></td> 
   <td>200</td> 
   <td>jl_root</td> 
  </tr> 
  <tr> 
   <td>ln_root</td> 
   <td>root</td> 
   <td>辽宁省</td> 
   <td></td> 
   <td>200</td> 
   <td>root</td> 
  </tr> 
  <tr> 
   <td>ln_ys</td> 
   <td>ln_root</td> 
   <td>辽宁省沈阳市</td> 
   <td></td> 
   <td>1000</td> 
   <td>ln_root</td> 
  </tr> 
  <tr> 
   <td>ln_sy_hp</td> 
   <td>ln_ys</td> 
   <td>辽宁省沈阳和平区</td> 
   <td></td> 
   <td>500</td> 
   <td>ln_ys</td> 
  </tr> 
  <tr> 
   <td>ln_ys_dd</td> 
   <td>ln_ys</td> 
   <td>辽宁省沈阳大东区</td> 
   <td></td> 
   <td>600</td> 
   <td>ln_ys</td> 
  </tr> 
  <tr> 
   <td>root_1</td> 
   <td>root</td> 
   <td>北京市</td> 
   <td></td> 
   <td>2000</td> 
   <td>root</td> 
  </tr> 
 </tbody> 
</table>

相当于是lead函数的简易实现。缺陷是只能查前一级。

with nayi as ( select t1.*, prior dept_id, connect_by_isleaf isleaf from nayi_180328_connect_test t1 where 1 = 1 start with t1.dept_id = 'root' connect by prior t1.dept_id = t1.parent_id ) select distinct t1.*, lead(t1.dept_id, 1) over(partition by connect_by_root(t1.dept_id) order by level) p_dept, lead(t1.dept_id, 2) over(partition by connect_by_root(t1.dept_id) order by level) p_dept_2 from nayi_180328_connect_test t1 start with t1.dept_id in (select v1.dept_id from nayi v1 where v1.isleaf = 1) connect by prior t1.parent_id = t1.dept_id ;

<table> 
 <thead> 
  <tr> 
   <th>DEPT_ID</th> 
   <th>PARENT_ID</th> 
   <th>DEPT_NAME</th> 
   <th>DEPT_RANK</th> 
   <th>VAL</th> 
   <th>P_DEPT</th> 
   <th>P_DEPT_2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ln_sy_hp</td> 
   <td>ln_ys</td> 
   <td>辽宁省沈阳和平区</td> 
   <td></td> 
   <td>500</td> 
   <td>ln_ys_dd</td> 
   <td>ln_root</td> 
  </tr> 
  <tr> 
   <td>jl_root</td> 
   <td>root</td> 
   <td>吉林省</td> 
   <td></td> 
   <td>0</td> 
   <td>root</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>root</td> 
   <td></td> 
   <td>全国</td> 
   <td></td> 
   <td>0</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>ln_ys_dd</td> 
   <td>ln_ys</td> 
   <td>辽宁省沈阳大东区</td> 
   <td></td> 
   <td>600</td> 
   <td>jl_jl</td> 
   <td>ln_root</td> 
  </tr> 
  <tr> 
   <td>jl_root</td> 
   <td>root</td> 
   <td>吉林省</td> 
   <td></td> 
   <td>0</td> 
   <td>ln_ys</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>ln_root</td> 
   <td>root</td> 
   <td>辽宁省</td> 
   <td></td> 
   <td>200</td> 
   <td>root</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>root</td> 
   <td></td> 
   <td>全国</td> 
   <td></td> 
   <td>0</td> 
   <td>root</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>root</td> 
   <td></td> 
   <td>全国</td> 
   <td></td> 
   <td>0</td> 
   <td>ln_root</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>jl_jl</td> 
   <td>jl_root</td> 
   <td>吉林省吉林市</td> 
   <td></td> 
   <td>200</td> 
   <td>jl_cc</td> 
   <td>root</td> 
  </tr> 
  <tr> 
   <td>ln_ys</td> 
   <td>ln_root</td> 
   <td>辽宁省沈阳市</td> 
   <td></td> 
   <td>1000</td> 
   <td>root</td> 
   <td>root</td> 
  </tr> 
  <tr> 
   <td>jl_cc</td> 
   <td>jl_root</td> 
   <td>吉林省长春市</td> 
   <td></td> 
   <td>500</td> 
   <td>ln_ys</td> 
   <td>root</td> 
  </tr> 
  <tr> 
   <td>ln_ys</td> 
   <td>ln_root</td> 
   <td>辽宁省沈阳市</td> 
   <td></td> 
   <td>1000</td> 
   <td>jl_root</td> 
   <td>root</td> 
  </tr> 
  <tr> 
   <td>root_1</td> 
   <td>root</td> 
   <td>北京市</td> 
   <td></td> 
   <td>2000</td> 
   <td>ln_sy_hp</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>root</td> 
   <td></td> 
   <td>全国</td> 
   <td></td> 
   <td>0</td> 
   <td>jl_root</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>ln_root</td> 
   <td>root</td> 
   <td>辽宁省</td> 
   <td></td> 
   <td>200</td> 
   <td>ln_root</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

#### siblings ####

保留树状结果顺序，并对兄弟节点进行排序

select t1.* from nayi_180328_connect_test t1 where 1 = 1 start with t1.dept_id = 'root' connect by prior t1.dept_id = t1.parent_id order siblings by t1.val desc ;

<table> 
 <thead> 
  <tr> 
   <th>DEPT_ID</th> 
   <th>PARENT_ID</th> 
   <th>DEPT_NAME</th> 
   <th>DEPT_RANK</th> 
   <th>VAL</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>root</td> 
   <td></td> 
   <td>全国</td> 
   <td></td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>root_1</td> 
   <td>root</td> 
   <td>北京市</td> 
   <td></td> 
   <td>2000</td> 
  </tr> 
  <tr> 
   <td>ln_root</td> 
   <td>root</td> 
   <td>辽宁省</td> 
   <td></td> 
   <td>200</td> 
  </tr> 
  <tr> 
   <td>ln_ys</td> 
   <td>ln_root</td> 
   <td>辽宁省沈阳市</td> 
   <td></td> 
   <td>1000</td> 
  </tr> 
  <tr> 
   <td>ln_ys_dd</td> 
   <td>ln_ys</td> 
   <td>辽宁省沈阳大东区</td> 
   <td></td> 
   <td>600</td> 
  </tr> 
  <tr> 
   <td>ln_sy_hp</td> 
   <td>ln_ys</td> 
   <td>辽宁省沈阳和平区</td> 
   <td></td> 
   <td>500</td> 
  </tr> 
  <tr> 
   <td>jl_root</td> 
   <td>root</td> 
   <td>吉林省</td> 
   <td></td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>jl_cc</td> 
   <td>jl_root</td> 
   <td>吉林省长春市</td> 
   <td></td> 
   <td>500</td> 
  </tr> 
  <tr> 
   <td>jl_jl</td> 
   <td>jl_root</td> 
   <td>吉林省吉林市</td> 
   <td></td> 
   <td>200</td> 
  </tr> 
 </tbody> 
</table>

#### sys\_connect\_by\_path ####

常使用生成的数据来简化其他查询

update ( select /*+ BYPASS_UJVC */ t1.*, t2.dept_path from nayi_180328_connect_test t1, (select t1.dept_id, sys_connect_by_path(t1.dept_id, '/') dept_path from nayi_180328_connect_test t1 where 1 = 1 start with t1.dept_id = 'root' connect by prior t1.dept_id = t1.parent_id) t2 where t1.dept_id = t2.dept_id ) t1 set t1.dept_rank = t1.dept_path ; commit;

<table> 
 <thead> 
  <tr> 
   <th>DEPT_ID</th> 
   <th>PARENT_ID</th> 
   <th>DEPT_NAME</th> 
   <th>DEPT_RANK</th> 
   <th>VAL</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>root</td> 
   <td></td> 
   <td>全国</td> 
   <td>/root</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>root_1</td> 
   <td>root</td> 
   <td>北京市</td> 
   <td>/root/root_1</td> 
   <td>2000</td> 
  </tr> 
  <tr> 
   <td>ln_root</td> 
   <td>root</td> 
   <td>辽宁省</td> 
   <td>/root/ln_root</td> 
   <td>200</td> 
  </tr> 
  <tr> 
   <td>ln_ys</td> 
   <td>ln_root</td> 
   <td>辽宁省沈阳市</td> 
   <td>/root/ln_root/ln_ys</td> 
   <td>1000</td> 
  </tr> 
  <tr> 
   <td>ln_sy_hp</td> 
   <td>ln_ys</td> 
   <td>辽宁省沈阳和平区</td> 
   <td>/root/ln_root/ln_ys/ln_sy_hp</td> 
   <td>500</td> 
  </tr> 
  <tr> 
   <td>ln_ys_dd</td> 
   <td>ln_ys</td> 
   <td>辽宁省沈阳大东区</td> 
   <td>/root/ln_root/ln_ys/ln_ys_dd</td> 
   <td>600</td> 
  </tr> 
  <tr> 
   <td>jl_root</td> 
   <td>root</td> 
   <td>吉林省</td> 
   <td>/root/jl_root</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>jl_jl</td> 
   <td>jl_root</td> 
   <td>吉林省吉林市</td> 
   <td>/root/jl_root/jl_jl</td> 
   <td>200</td> 
  </tr> 
  <tr> 
   <td>jl_cc</td> 
   <td>jl_root</td> 
   <td>吉林省长春市</td> 
   <td>/root/jl_root/jl_cc</td> 
   <td>500</td> 
  </tr> 
 </tbody> 
</table>

例如通过dept\_rank列来查询辽宁省的全部节点

select * from nayi_180328_connect_test t1 start with t1.dept_id = 'ln_root' connect by prior t1.dept_id = t1.parent_id ;

<table> 
 <thead> 
  <tr> 
   <th>DEPT_ID</th> 
   <th>PARENT_ID</th> 
   <th>DEPT_NAME</th> 
   <th>DEPT_RANK</th> 
   <th>VAL</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ln_root</td> 
   <td>root</td> 
   <td>辽宁省</td> 
   <td>/root/ln_root</td> 
   <td>200</td> 
  </tr> 
  <tr> 
   <td>ln_ys</td> 
   <td>ln_root</td> 
   <td>辽宁省沈阳市</td> 
   <td>/root/ln_root/ln_ys</td> 
   <td>1000</td> 
  </tr> 
  <tr> 
   <td>ln_sy_hp</td> 
   <td>ln_ys</td> 
   <td>辽宁省沈阳和平区</td> 
   <td>/root/ln_root/ln_ys/ln_sy_hp</td> 
   <td>500</td> 
  </tr> 
  <tr> 
   <td>ln_ys_dd</td> 
   <td>ln_ys</td> 
   <td>辽宁省沈阳大东区</td> 
   <td>/root/ln_root/ln_ys/ln_ys_dd</td> 
   <td>600</td> 
  </tr> 
 </tbody> 
</table>

最后再举一些我平时练习时使用的例子

枚举法求最大公约数

with a1 as (select level num from dual connect by level <= 100), a2 as (select level num from dual connect by level <= 64) select max(t1.n1) greatest_divisor from (select a1.num n1, a2.num n2 from a1, a2 where 1 = 1 and mod(100, least(a1.num, a2.num)) = 0 and mod(64, least(a1.num, a2.num)) = 0) t1 where t1.n1 = t1.n2 ;

结果为4。

按组去除相同前缀

with tab1 as ( select '12388554gf' str, 1 group_id from dual union all select '12311' str, 1 from dual union all select '1234rg' str, 1 from dual union all select '1238822f' str, 1 from dual union all select 'asdf123' str, 2 from dual union all select 'asdf789' str, 2 from dual), tab2 as ( select t1.str, t1.group_id, level - 1 ll, substr(t1.str, 1, level - 1) sub_str, count(1) over(partition by t1.group_id, level, substr(t1.str, 1, level - 1)) max_num from (select t1.str, t1.group_id, min(length(t1.str)) over(partition by t1.group_id) min_length, count(1) over(partition by t1.group_id) row_num from tab1 t1) t1 connect by level <= min_length + 1 and prior t1.str = t1.str and prior sys_guid() is not null), tab3 as ( select distinct first_value(t1.ll) over(partition by t1.group_id order by t1.max_num desc, t1.ll desc) sub_num, t1.group_id from tab2 t1) select t1.str, substr(t1.str, (select max(sub_num) from tab3 v1 where v1.group_id = t1.group_id) + 1) sub_str, t1.group_id from tab1 t1 ;

<table> 
 <thead> 
  <tr> 
   <th>STR</th> 
   <th>SUB_STR</th> 
   <th>GROUP_ID</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>12388554gf</td> 
   <td>88554gf</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>12311</td> 
   <td>11</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>1234rg</td> 
   <td>4rg</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>1238822f</td> 
   <td>8822f</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>asdf123</td> 
   <td>123</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>asdf789</td> 
   <td>789</td> 
   <td>2</td> 
  </tr> 
 </tbody> 
</table>

### 总结 ###

递归查询主要使用的地方

*  通过递归特点生成数据。
 *  构建树状表格。
 *  通过sys\_connect\_by\_path生成的字段做其他处理。
 *  对数据进行树状梳理，使用各种关键字与函数实现想要的结果。