数据库中有一个表,结构很简单,如下所示:
表名:TBInvoice
字段:id
name
parent_id
每一行记录表示一个发票,parent_id表示它的父对象id,假设现在表中有如下数据:
id name parent_id
1 张三
2 李四 1
3 王五 1
4 张一 2
5 王三 2
6 武松 3
7 李四 4
8 朱大 5
9 曾子 5
10 常三 6
这样就形成了如下一个树,我们暂且称它们为一个家族吧。
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
现在有这样一个需求,给定一个id,要查出这个id所述家族中的所有id,比方说无论是给定1-10中的哪一个id,都必须查出这个家族中的所有id,并且不能出现别的家族中的id。这里我们可以使用Oracle自带的树状查询函数:
SELECT …..
CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2}
[START WITH];
现在以id=5为例,要求查出它所属家族中的所有id。
思路:在家族书中进行回溯,向上查找,得到祖先id
首先我们用如下的语句:
Select id from TBInvoice connect by id=prior parentid start with id=5,这条语句从id=5处向上查找,一直到祖先节点为止。在本表中所有的顶层节点的parentid都是null
接着我们利用下面的语句得到祖先节点的id
Select id from
(Select id from TBInvoice connect by id=prior parentid start with id=5)
Where parentid is null
这样我们就得到了id 5所属家族的祖先id。
最后,我们就需要根据祖先id向下进行查找,得到所有的子id了
Select id from TBInvoice connect by prior id=parentid start with id=
(Select id from
(Select id from TBInvoice connect by id=prior parentid start with id=5)
Where parentid is null)
如此一来我们就可以得到id 5所属家族中的所有id了。
在整个过程,起关键作用的就是prior这个关键字,它代表什么意思呢?它的英文意思为“优先的”,在这里可以这样理解:
当prior在parentid之前时,说明parentid优先,如果start with id=5,那么从id=5的那条记录开始查找,后面的记录都必须是id等于前面记录的parentid,这样就表明是向上进行查找,追溯它所有的祖先。
当prior在id之前时,说明id优先,如果start with id=5,那么从id=5的那条记录开始查找,后面的记录都必须是parentid=前面记录的id,这就相当于是向下进行,寻找它所有的子孙。