- 浏览: 417238 次
- 性别:
- 来自: 深圳
文章分类
最新评论
-
Glogo:
楼主您好,我在试验您的代码的时候发现新开的三个子线程并没有一直 ...
java 高并发 ReentrantLock -- 可重入的锁 -
univasity:
最近发觉也被限速了,投诉一下就好一会~~ 看来明天又要和电信M ...
ADSL上网速度慢 都是帐号限速惹的祸 -
liuyuanhui0301:
java 高并发 ReentrantLock -- 可重入的锁 -
dang_java:
呵.很好的说明文档.
JXTA技术与应用发展 -
helloqidi:
谢谢,学习了
SQL中exists和in的区别
A simple example
In the following example, the table from which that data is selected consists of just these attributes: parent and child. We make sure (by means of a unique constraint) that the child is uniqe within the table. This is just like in the real life where (as of yet) a child cannot have two different mothers.
The data filled into the table is such that a the sum over the children with the same parent is the value of the parent:
set feedback off
create table test_connect_by (
parent number,
child number,
constraint uq_tcb unique (child)
);
5 = 2+3
insert into test_connect_by values ( 5, 2);
insert into test_connect_by values ( 5, 3);
18 = 11+7
insert into test_connect_by values (18,11);
insert into test_connect_by values (18, 7);
17 = 9+8
insert into test_connect_by values (17, 9);
insert into test_connect_by values (17, 8);
26 = 13+1+12
insert into test_connect_by values (26,13);
insert into test_connect_by values (26, 1);
insert into test_connect_by values (26,12);
15=10+5
insert into test_connect_by values (15,10);
insert into test_connect_by values (15, 5);
38=15+17+6
insert into test_connect_by values (38,15);
insert into test_connect_by values (38,17);
insert into test_connect_by values (38, 6);
38, 26 and 18 have no parents (the parent is null)
insert into test_connect_by values (null, 38);
insert into test_connect_by values (null, 26);
insert into test_connect_by values (null, 18);
Now, let's select the data hierarchically:
select lpad(' ',2*(level-1)) || to_char(child) s
from test_connect_by
start with parent is null
connect by prior child = parent;
This select statement results in:
38
15
10
5
2
3
17
9
8
6
26
13
1
12
18
11
7
set feedback off create table test_connect_by ( parent number, child number, constraint uq_tcb unique (child) );
insert into test_connect_by values ( 5, 2); insert into test_connect_by values ( 5, 3);
insert into test_connect_by values (18,11); insert into test_connect_by values (18, 7);
insert into test_connect_by values (17, 9); insert into test_connect_by values (17, 8);
insert into test_connect_by values (26,13); insert into test_connect_by values (26, 1); insert into test_connect_by values (26,12);
insert into test_connect_by values (15,10); insert into test_connect_by values (15, 5);
insert into test_connect_by values (38,15); insert into test_connect_by values (38,17); insert into test_connect_by values (38, 6);
insert into test_connect_by values (null, 38); insert into test_connect_by values (null, 26); insert into test_connect_by values (null, 18);
select lpad(' ',2*(level-1)) || to_char(child) s from test_connect_by start with parent is null connect by prior child = parent;
38 15 10 5 2 3 17 9 8 6 26 13 1 12 18 11 7
Interpreting connect by statements
How must a
start with ... connect by
select statement be read and interpreted? If Oracle encounters such an SQL statement, it proceeds as described in the following pseude code. for rec in (select * from some_table) loop if FULLFILLS_START_WITH_CONDITION(rec) then RECURSE(rec, rec.child); end if; end loop; procedure RECURSE (rec in MATCHES_SELECT_STMT, parent_id IN field_type) is begin APPEND_RESULT_LIST(rec); for rec_recurse in (select * from some_table) loop if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.id, parent_id) then RECURSE(rec_recurse,rec_recurse.id); end if; end loop; end procedure RECURSE;
Thanks to Frank Trenkamp who spotted an error in the logic in the above pseudo code and corrected it.
Thanks also to Abhishek Ghose who made me think about a better way to describe the logic.
Pruning branches
Sometimes, it might be a requirement to only partially retrieve a hierarchical tree and to prune branches. Here, a tree is filled. Each child is the number of its parent plus a new digit on the right side.
create table prune_test ( parent number, child number ); insert into prune_test values (null, 1); insert into prune_test values (null, 6); insert into prune_test values (null, 7); insert into prune_test values ( 1, 12); insert into prune_test values ( 1, 14); insert into prune_test values ( 1, 15); insert into prune_test values ( 6, 61); insert into prune_test values ( 6, 63); insert into prune_test values ( 6, 65); insert into prune_test values ( 6, 69); insert into prune_test values ( 7, 71); insert into prune_test values ( 7, 74); insert into prune_test values ( 12, 120); insert into prune_test values ( 12, 124); insert into prune_test values ( 12, 127); insert into prune_test values ( 65, 653); insert into prune_test values ( 71, 712); insert into prune_test values ( 71, 713); insert into prune_test values ( 71, 715); insert into prune_test values ( 74, 744); insert into prune_test values ( 74, 746); insert into prune_test values ( 74, 748); insert into prune_test values ( 712,7122); insert into prune_test values ( 712,7125); insert into prune_test values ( 712,7127); insert into prune_test values ( 748,7481); insert into prune_test values ( 748,7483); insert into prune_test values ( 748,7487);
Now, we want to retrieve the tree, but prune everything below the branch 1 and 71. It would be false to put these into a where clause of the sql statement, rather, it belongs to the connect by clause:
select lpad(' ', 2*level) || child from prune_test start with parent is null connect by prior child=parent and parent not in (1, 71);
This returns:
1 6 61 63 65 653 69 7 71 74 744 746 748 7481 7483 7487
See also another example for pruning.
Do two items stand in a ancestor descendant relationship
Sometimes, one want's to know if two items are in an ancestor descendant relationship, that is if XYZ as grandfather, or grand-grandfather, or ... of ABC. The following template of a query can be used to determine that.
set feedback off drop table parent_child; create table parent_child(parent_ varchar2(20), child_ varchar2(20)); insert into parent_child values (null, 'a') insert into parent_child values ( 'a', 'af'); insert into parent_child values ( 'a', 'ab'); insert into parent_child values ( 'a', 'ax'); insert into parent_child values ( 'ab', 'abc'); insert into parent_child values ( 'ab', 'abd'); insert into parent_child values ( 'ab', 'abe'); insert into parent_child values ('abe','abes'); insert into parent_child values ('abe','abet'); insert into parent_child values ( null, 'b'); insert into parent_child values ( 'b', 'bg'); insert into parent_child values ( 'b', 'bh'); insert into parent_child values ( 'b', 'bi'); insert into parent_child values ( 'bi', 'biq'); insert into parent_child values ( 'bi', 'biv'); insert into parent_child values ( 'bi', 'biw');
The following query 'asks' for a parent and a supposed child (grand child, grand grand child) and answers the question if the are indeed in an ancester successor relationship.
set verify off select case when count(*) > 0 then '&&parent is an ancestor of &&child' else '&&parent is no ancestor of &&child' end "And here's the answer" from parent_child where child_ = '&&child' start with parent_ = '&&parent' connect by prior child_ = parent_; undefine child undefine parent
Features of 9i
sys_connect_by_path
With sys_connect_by_path it is possible to show the entire path from the top level down to the 'actual' child:
Using hierarchical result sets
With this technique, it is possible to show all kind of hierarchical data relations. Here is an example that lists privileges, roles and users in their hierarchical relation.
See also flat hiearchy.
connect_by_root
connect_by_root is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
I have yet to dig into this subject and will write about it when things become clearer.
connect_by_is_leaf
connect_by_isleaf is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
I have yet to dig into this subject and will write about it when things become clearer.
connect_by_iscycle
connect_by_is_cycle is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
I have yet to dig into this subject and will write about it when things become clearer.
Thanks
Thanks to Peter Bruhn, Jonathan Schmalze, Jeff Jones, Keith Britch and Fabian Iturralde who each pointed out an error, misstake or typo on this page.
Further links
发表评论
-
mysql查询优化之索引类型、最左前缀
2012-03-22 00:42 1050一、什么是索引? 索引用来快速地寻找那些具有特定值的记录, ... -
Checkpoint not Complete
2009-04-30 15:36 1475alter_ORCL.log如下: Wed Jun 25 0 ... -
How to resize redo log file?
2009-04-30 15:33 1051I wrote a script named 'checkRe ... -
Oracle: REDO&UNDO
2009-04-25 13:41 1018在这里会介绍UNDO,REDO ... -
DBCP,数据库断开自动连接的问题及解决方法
2009-04-20 13:34 2700以前公司开发的一个项 ... -
Oracle : How to drop database
2009-04-06 01:05 1011字号: 大大 中中 小小 D:\>set or ... -
执行计划和执行顺序
2009-04-02 17:53 919如果要了解执行计划和执行顺序,必须理解执行计划的 ... -
Oracle timestamp
2009-02-01 10:47 3052select systimestamp from dual s ... -
Oracle日期函数与应用
2009-01-19 16:26 1119相信很多人都有过统计某些数据的经历,比如,要统计财务的情况 ... -
start with ... connect by用法简介
2008-12-22 17:17 1218sql有向图问题期待新解决方案 /************* ... -
利用伪列算出每个月那几天是星期天(ORACLE)
2008-12-22 16:05 15021.首先我们要用到LEVEL .... CONNE ... -
Oracle层次查询和分析应用在号段选取
2008-12-22 15:33 10811. 问题的提出 在实际工作中,我们常常会碰到号段选取的 ... -
有关Oracle表分区进行(DML)维护后对索引的影响的分析
2008-12-15 14:30 2193今天对数字电视系统的 ... -
Oracle表分区和索引分区精彩汇总
2008-12-15 14:29 1948分区概述 为了简化数据库大表的管理,例如在数据仓库中 ... -
为什么有时Oracle数据库不用索引来查找数据?
2008-12-15 14:24 1225当你运用SQL语言,向数 ... -
怎样成长为一个真正的Oracle DBA
2008-12-15 12:59 919本文网址:http://bbs.bitscn.com/1674 ... -
MYSQL编码问题
2008-12-04 21:56 622一.原理篇Mysql的字符 ... -
ORACEL SQL 性能优化
2008-10-16 15:18 11911. 选用适合的ORACLE优化器 ORACLE的优化器共有 ... -
mysql 的mysql数据库
2008-09-20 17:06 1003授权表的用途: ... -
Hibernate注释简介
2008-09-19 13:54 1060在过去几年里,Hibernate不断发展,几乎成为Java数据 ...
相关推荐
Oracle 查询树型关系是指使用 START WITH 和 CONNECT BY 子句来实现 SQL 的层次查询。从 Oracle 9i 开始,可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 ...
Oracle 连接查询是指使用 START WITH 和 CONNECT BY 语句来实现递归查询的方法,这种方法可以生成树形结构的数据。在 Oracle 中,START WITH 语句用于指定递归查询的开始记录,而 CONNECT BY 语句用于指定递归查询的...
[ WHERE condition ][ [ START WITH condition ] CONNECT BY condition [ ORDER SIBLINGS BY expression ] ] ``` 其中,`START WITH`子句用于指定查询的起始节点,`CONNECT BY`子句用于指定查询的递归条件。 ...
作者通过总结各自多年的软件开发和教学培训经验,与大家分享了掌握Oracle SQL所独有的丰富功能的技巧所在,内容涵盖SQL执行、联结、集合、分析函数、子句、事务处理等多个方面。读者可以学习到以下几个方面的技巧:...
Oracle使用递归查询。查询树结构的sql。在Oracle中,递归查询要用到start with ……connect by prior……
oracle中的select语句可以用START WITH…CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是: 代码如下:select * from tablename start with cond1 connect by cond2 where cond3;...
查询命令:select CONNECT_BY_ISCYCLE, dirindex, fatherindex, RPAD(' ',2*(LEVEL-1)) || dirname from t_tonedirlib start with fatherindex = 666 connect by NOCYCLE fatherindex = prior dirindex; 七、...
其中,`START WITH` 子句指定了递归查询的起始点,`CONNECT BY` 子句指定了递归查询的连接条件。 在上面的示例代码中,我们创建了一个名为 `T_DEPT_HP` 的表,并插入了一些示例数据。然后,我们可以使用递归函数来...
最后,我们使用 `sys_connect_by_path()` 函数来拼接分组后的数据,并使用 `start with` 子句和 `connect by` 子句来实现递归操作: ```sql select No, ltrim(max(sys_connect_by_path(Value, ';')), ';') as ...
Oracle、MySQL、SQL Server都是常用的数据库管理系统,它们提供了多种方式来管理和操作数据库。在本文中,我们将介绍Oracle、MySQL、SQL Server查看当前所有数据库及数据库基本操作命令。 一、Oracle数据库管理系统...
SQL>START test SQL>@test 常用SQL*Plus语句 a、表的创建、修改、删除 创建表的命令格式如下: create table 表名 (列说明列表); 为基表增加新列命令如下: ALTER TABLE 表名 ADD (列说明列表) 例:为test表...
TNS:listener Could Not Resolve SID Given in Connect Descriptor 484 Insufficient Privileges During Grants484 Escape Character in Your Statement--Invalid Character 485 Cannot Create Operating System ...
start (9) 退出监听器控制台,命令:exit (10) 重启数据库结束 ----------------------------------- Linux重启oracle数据库方法4(自已写脚本) 1)启动脚本(dbstart.sh) lsnrctl start sqlplus /nolog < connect / ...
START WITH ID = 0 AND STATEMENT_ID = user_define CONNECT BY PRIOR ID = parent_id AND STATEMENT_ID = user_define 示例 如要测试下面SQL: SELECT c.short, a.cday, a.card_no, a.qty FROM sales....
start with rnNext is null connect by rnNext = prior rnFirst group by No; ``` 最终的结果如下: | NO | VALUE | NAME | | --- | --- | --- | | 1 | a;b;c;d | 测试 1; 测试 2; 测试 3; 测试 4 | | 2 | e | ...
案例:sql>@ d:\a.sql 或者 sql>start d:a.sql (2) edit 说明:该命令可以编辑指定的sql脚本。 案例:sql>edit d:\a.sql (3) spool 说明:该命令可以将sql*plus屏幕上的内容输出到指定文件中去。 案例:sql>spool d:...
在"计算机管理"控制台中->系统工具->本地用户和组->组中,增加名称为"ORA_DBA",描述为"Members can connect to the Oracle database as a DBA without a password"的组,并将系统管理员用户如"Administrator"添加到...
使用 PB、 PL/SQL 连接 64 位 Oracle 的方法 在 64 位操作系统下,使用 PB、 PL/SQL 连接 64 位 Oracle 需要注意一些重要的配置步骤。下面将详细介绍连接 64 位 Oracle 的方法。 方法 1:使用 Oracle Instant ...