表testTab,自己引用自己,结构如下:
id parentId(父id) isnotLeaf(是否叶子,不是时值为1,否则为null)
1 null 1
2 1 1
3 1 null
4 2 null
5 3 null
................以下略求出表中isnotLeaf值为null,同时又有子节点的所有记录(上面id为3的记录就满足这个条件)
id parentId(父id) isnotLeaf(是否叶子,不是时值为1,否则为null)
1 null 1
2 1 1
3 1 null
4 2 null
5 3 null
................以下略求出表中isnotLeaf值为null,同时又有子节点的所有记录(上面id为3的记录就满足这个条件)
from testTab A join
(
select parentId
from testTab
where isnotLeaf is null
) B on A.id = B.parentId
where A.isnotLeaf is null
---------- ---------- ----------
1 1SQL> insert into tbl select 2,1,1 from dual
2 union all select 3,1,null from dual
3 union all select 4,2,null from dual
4 union all select 5,3,null from dual;4 rows inserted
SQL> select * from tbl where isnoleaf is null; ID PARENTID ISNOLEAF
---------- ---------- ----------
3 1
4 2
5 3 SQL> select * from tbl where isnoleaf is null and parentid in(select distinct isnoleaf from tbl); ID PARENTID ISNOLEAF
---------- ---------- ----------
3 1
SQL> create table tbl(id number,parentid number,isnoleaf number);Table created
where isnotLeaf is null
and exists(select 1 from testTab where g.id=parentId)