Oracle的start with对于处理层级关系非常方便。
start with
connect by prior upper_indicator_id=indicator_id
查询上级节点的ID
start with
connect by prior indicator_id=upper_indicator_id
查询出下级节点的ID
下面的SQL:
select * from table1
start with (talbe1.field1='K0001' or talbe1.field1='K0002' )
connect by prior upper_indicator_id=indicator_id
一般我们这样书写,只是查询出某些节点下所有的上级节点,但是如果这些节点不是已知的,而是从表查询出的,就无法查询,可以采用下面的写法:
select * from table1 left jon table2
on talbe1.field1=table2.filed1
start with (talbe1.field1=table2.filed1)
connect by prior upper_indicator_id=indicator_id
这种写法其实只是为table1增加了一列而已。