刚才遇到个connect by level
我预期的结果,所得结果数量应该就是level
level=3时
(1)
grandparent:10
parent:10、20、30、40
child :10、20、30、40
共16条
(2)
grandparent:10
parent:10
child :10、20、30、40
共4条
(3)
grandparent:10
parent:10
child :10
共1条
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTINGaa NEW YORK
20 RESEARCHaa DALLAS
30 SALESaa CHICAGO
40 OPERATIONSaa BOSTON
------ -------------- -------------
10 ACCOUNTINGaa NEW YORK
20 RESEARCHaa DALLAS
30 SALESaa CHICAGO
40 OPERATIONSaa BOSTON
SQL> select * from dept where deptno=10 connect by level<2;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTINGaa NEW YORK
------ -------------- -------------
10 ACCOUNTINGaa NEW YORK
SQL> select dept.*,level from dept where deptno=10 connect by level<3;
DEPTNO DNAME LOC LEVEL
------ -------------- ------------- ----------
10 ACCOUNTINGaa NEW YORK 1
10 ACCOUNTINGaa NEW YORK 2
10 ACCOUNTINGaa NEW YORK 2
10 ACCOUNTINGaa NEW YORK 2
10 ACCOUNTINGaa NEW YORK 2
------ -------------- ------------- ----------
10 ACCOUNTINGaa NEW YORK 1
10 ACCOUNTINGaa NEW YORK 2
10 ACCOUNTINGaa NEW YORK 2
10 ACCOUNTINGaa NEW YORK 2
10 ACCOUNTINGaa NEW YORK 2
SQL> select dept.*,level from dept where deptno=10 connect by level<4;
DEPTNO DNAME LOC LEVEL
------ -------------- ------------- ----------
10 ACCOUNTINGaa NEW YORK 1
10 ACCOUNTINGaa NEW YORK 2
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 2
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 2
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 2
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
------ -------------- ------------- ----------
10 ACCOUNTINGaa NEW YORK 1
10 ACCOUNTINGaa NEW YORK 2
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 2
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 2
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 2
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
10 ACCOUNTINGaa NEW YORK 3
DEPTNO DNAME LOC LEVEL
------ -------------- ------------- ----------
10 ACCOUNTINGaa NEW YORK 3
------ -------------- ------------- ----------
10 ACCOUNTINGaa NEW YORK 3
21 rows selected
我预期的结果,所得结果数量应该就是level
看了一下官方文档中CONNECT_BY_ISCYCLE的解释,(在11g sql reference 中37页图“Figure 2–1 Hierarchical Tree”),渐渐明白了原因。
我们可以将语句中的level理解为对“遗传关系树”的层数限制。具体来说:
level=1时
parent:10
child :10
我们可以将语句中的level理解为对“遗传关系树”的层数限制。具体来说:
level=1时
parent:10
child :10
level=2时
(1)
parent:10
child :10、20、30、40
(2)
parent:10
child :10
(1)
parent:10
child :10、20、30、40
(2)
parent:10
child :10
level=3时
(1)
grandparent:10
parent:10、20、30、40
child :10、20、30、40
共16条
(2)
grandparent:10
parent:10
child :10、20、30、40
共4条
(3)
grandparent:10
parent:10
child :10
共1条
最后得到16+4+1
这样推测,level<5时,应该是64+16+4+1=85条
证明:
SQL> select count(1) from dept where deptno=10 connect by level<5;
SQL> select count(1) from dept where deptno=10 connect by level<5;
COUNT(1)
----------
85
----------
85
SQL> select count(1) from dept where deptno=10 connect by level<6;
COUNT(1)
----------
341
这是集合的思想得到的。level则得到的结果集数量为:
m^(n-2)+m^(n-3)+……+m^2+m+1
----------
341
这是集合的思想得到的。level则得到的结果集数量为:
m^(n-2)+m^(n-3)+……+m^2+m+1
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-768165/,如需转载,请注明出处,否则将追究法律责任。
上一篇:
建模的一些体会和总结
下一篇:
外键创建的认识.TXT
![user_pic_default.png](http://blog.itpub.net/images/user_pic_default.png)
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%>
<%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%}%> <%if(items[i].items.total > 5) { %>
<%}%>
<%=items[i].items.items[j].createtime%>
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>: <%=items[i].items.items[j].content%>
还有<%=items[i].items.total-5%>条评论
) data-count=1 data-flag=true>点击查看
<%}%>
转载于:http://blog.itpub.net/26451536/viewspace-768165/