connect by prior start with语句详解.docx
- 文档编号:29856459
- 上传时间:2023-07-27
- 格式:DOCX
- 页数:21
- 大小:24.73KB
connect by prior start with语句详解.docx
《connect by prior start with语句详解.docx》由会员分享,可在线阅读,更多相关《connect by prior start with语句详解.docx(21页珍藏版)》请在冰豆网上搜索。
connectbypriorstartwith语句详解
connectbypriorstartwith语句详解
语法:
在oracle中的select语句可以用STARTWITH...CONNECTBYPRIOR子句实现递归查询,connectby是结构化查询中用到的,其基本语法是:
select...fromtablename
startwithcond1
connectbycond2
wherecond3;
简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段:
id,parentid那么通过表示每一条记录的parent是谁,就可以形成一个树状结构。
用上述语法的查询可以取得这棵树的所有记录。
其中:
COND1是根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树。
COND2是连接条件,其中用PRIOR表示上一条记录,比如CONNECTBYPRIORID=PRAENTID就是说上一条记录的ID是本条记录的PRAENTID,即本记录的父亲是上一条记录。
COND3是过滤条件,用于对返回的所有记录进行过滤。
Oracle的树形查找语句connectbyprior...startwith...
Startwith后面跟的是开始结点即从什么位置开始查找。
可以有多个起始结点,Connectby 连接有父子关系的两个节点id=parent_id;怎么去遍历这棵树prior是关键,如果prior在parent_id这一侧(id=priorparent_id)则说明向父节点查找即自底向上查找,如果prior在id这一侧(priorid=parent_id)则说明向子结点方向查找即自顶向下查找。
遍历方式:
先序遍历;
通过STARTWITH...CONNECTBY...子句来实现SQL的层次查询.自从Oracle9i开始,可以通过SYS_CONNECT_BY_PATH函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。
自从Oracle10g中,还有其他更多关于层次查询的新特性。
例如,有的时候用户更关心的是每个层次分支中等级最低的内容。
那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。
如果是叶子就会在伪列中显示“1”,如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示“0”。
在Oracle10g之前的版本中,如果在你的树中出现了环状循环(如一个孩子节点引用一个父亲节点),Oracle就会报出一个错误提示:
“ORA-01436:
CONNECTBYloopinuserdata”。
如果不删掉对父亲的引用就无法执行查询操作。
而在Oracle10g中,只要指定“NOCYCLE”就可以进行任意的查询操作。
与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE,如果在当前行中引用了某个父亲节点的内容并在树中出现了循环,那么该行的伪列中就会显示“1”,否则就显示“0”。
--创建测试表,增加测试数据
Sql代码
1.create table test(superid varchar2(20),id varchar2(20));
2.
3.insert into test values('0','1');
4.insert into test values('0','2');
5.
6.insert into test values('1','11');
7.insert into test values('1','12');
8.
9.insert into test values('2','21');
10.insert into test values('2','22');
11.
12.insert into test values('11','111');
13.insert into test values('11','112');
14.
15.insert into test values('12','121');
16.insert into test values('12','122');
17.
18.insert into test values('21','211');
19.insert into test values('21','212');
20.
21.insert into test values('22','221');
22.insert into test values('22','222');
23.
24.commit;
25.
26.--层次查询示例
27.select level||'层',lpad(' ',level*5)||id id
28.from test
29.start with superid = '0' connect by prior id=superid;
30.
31.select level||'层',connect_by_isleaf,lpad(' ',level*5)||id id
32.from test
33.start with superid = '0' connect by prior id=superid;
34.--功能:
实现按照superid分组,把id用";"连接起来
35.--实现:
以下两个例子都是通过构造2个伪列来实现connect by连接的。
36.
37./*------method one------*/
38.select superid,ltrim(max(sys_connect_by_path(id,';')),';') from(
39.select superid,id,row_number() over(partition by superid order by superid) id1,
40.row_number() over(order by superid) + dense_rank() over(order by superid) id2
41.from test
42.)
43.start with id1=1 connect by prior id2 = id2 -1
44.group by superid order by superid;
45.
46./*------method two------*/
47.select distinct superid,ltrim(first_value(id) over(partition by superid order by l desc),';')
48.from(
49.select superid,level l,sys_connect_by_path(id,';') id
50.from(
51.select superid,id,superid||rownum parent_rn,superid||to_char(rownum-1) rn
52.from test
53.)
54.connect by prior parent_rn = rn
55.);
56.
57.--下面的例子实现把一个整数的各个位上的数字相加,通过这个例子我们再次理解connect by.
58.
59.create or replace function f_digit_add(innum integer) return number
60.is
61.outnum integer;
62.begin
63.if innum<0 then
64.return 0;
65.end if;
66.select sum(nm) into outnum from(
67.select substr(innum,rownum,1) nm from dual connect by rownum 68.); 69.return outnum; 70.end f_digit_add; 71./ 72. 73.select f_digit_add(123456) from dual; createtabletest(superidvarchar2(20),idvarchar2(20)); insertintotestvalues('0','1'); insertintotestvalues('0','2'); insertintotestvalues('1','11'); insertintotestvalues('1','12'); insertintotestvalues('2','21'); insertintotestvalues('2','22'); insertintotestvalues('11','111'); insertintotestvalues('11','112'); insertintotestvalues('12','121'); insertintotestvalues('12','122'); insertintotestvalues('21','211'); insertintotestvalues('21','212'); insertintotestvalues('22','221'); insertintotestvalues('22','222'); commit; --层次查询示例 selectlevel||'层',lpad('',level*5)||idid fromtest startwithsuperid='0'connectbypriorid=superid; selectlevel||'层',connect_by_isleaf,lpad('',level*5)||idid fromtest startwithsuperid='0'connectbypriorid=superid; --给出两个以前在"数据库字符串分组相加之四"中的例子来理解startwith...connectby... --功能: 实现按照superid分组,把id用";"连接起来 --实现: 以下两个例子都是通过构造2个伪列来实现connectby连接的。 /*------methodone------*/ selectsuperid,ltrim(max(sys_connect_by_path(id,';')),';')from( selectsuperid,id,row_number()over(partitionbysuperidorderbysuperid)id1, row_number()over(orderbysuperid)+dense_rank()over(orderbysuperid)id2 fromtest ) startwithid1=1connectbypriorid2=id2-1 groupbysuperidorderbysuperid; /*------methodtwo------*/ selectdistinctsuperid,ltrim(first_value(id)over(partitionbysuperidorderbyldesc),';') from( selectsuperid,levell,sys_connect_by_path(id,';')id from( selectsuperid,id,superid||rownumparent_rn,superid||to_char(rownum-1)rn fromtest ) connectbypriorparent_rn=rn ); --下面的例子实现把一个整数的各个位上的数字相加,通过这个例子我们再次理解connectby. createorreplacefunctionf_digit_add(innuminteger)returnnumber is outnuminteger; begin ifinnum<0then return0; endif; selectsum(nm)intooutnumfrom( selectsubstr(innum,rownum,1)nmfromdualconnectbyrownum ); returnoutnum; endf_digit_add; / selectf_digit_add(123456)fromdual; /******************************************************************** 下面是关于SQL解决有向图问题,在这个例子中作者提到的错误 select*fromfaresconnectbypriorarrive=departstartwithdepart='LHR'; ERROR: ORA-01436: CONNECTBYloopinuserdata 在oracle10g以上版本可以利用connectby的nocycle参数来解。 有兴趣的朋友研究用一条sql实现有向图问题! *************/ 一个常见的高级计算机科学问题可以在“有向图”的范畴之下描述。 有向图是由一组向量和边所连接的一组有限的节点。 例如,一个节点可以想象为一座“城市”,而每个向量可以想象为两座城市间的一个“航线”。 有很多算法和论文讲到如何解决每种可能路线的遍历问题以及寻找最短路径或者最小代价路径的问题。 这些算法中大部分都是过程化的,或者是使用递归方面来解决的。 然而SQL的声明性语言使得解决复杂的有向图问题更加容易,而且不需要很多代码。 让我们以两座城市之间的航线为例子,创建一个表保存一些假想数据: Sql代码 1.create table airports 2.( 3.code char(3) constraint airports_pk primary key, 4.description varchar2(200) 5.); 6. 7.insert into airports values ('LHR','London Heathrow, UK'); 8.insert into airports values ('JFK','New York-Kennedy, USA'); 9.insert into airports values ('GRU','Sao Paulo, Brazil'); 10. 11.create table fares 12.( 13.depart char(3), 14.arrive char(3), 15.price number, 16.constraint fares_pk primary key (depart,arrive), 17.constraint fares_depart_fk foreign key (depart) references airports, 18.constraint fares_arrive_fk foreign key (arrive) references airports 19.); 20. 21.insert into fares values('LHR','JFK',700); 22.insert into fares values('JFK','GRU',600); 23.insert into fares values('LHR','GRU',1500); 24.insert into fares values('GRU','LHR',1600); createtableairports ( codechar(3)constraintairports_pkprimarykey, descriptionvarchar2(200) ); insertintoairportsvalues('LHR','LondonHeathrow,UK'); insertintoairportsvalues('JFK','NewYork-Kennedy,USA'); insertintoairportsvalues('GRU','SaoPaulo,Brazil'); createtablefares ( departchar(3), arrivechar(3), pricenumber, constraintfares_pkprimarykey(depart,arrive), constraintfares_depart_fkforeignkey(depart)referencesairports, constraintfares_arrive_fkforeignkey(arrive)referencesairports ); insertintofaresvalues('LHR','JFK',700); insertintofaresvalues('JFK','GRU',600); insertintofaresvalues('LHR','GRU',1500); insertintofaresvalues('GRU','LHR',1600); 不能使用CONNECTBY语法来解决如何从伦敦到圣保罗,因为在图中有数据产生一个环(从圣保罗飞回): Sql代码 1.select * from fares connect by prior arrive = depart start with depart = 'LHR'; select*fromfaresconnectbypriorarrive=departstartwithdepart='LHR'; ERROR: ORA-01436: CONNECTBYloopinuserdata 要解决有向图问题,我们需要创建一个临时表来保存两个节点之间所有可能的路径。 我们必须注意不复制已经处理过的路径,而且在这种情况下,我们不想路径走回开始处的同一个地点。 我还希望跟踪到达目的地所需航程的数目,以及所走路线的描述。 临时表使用以下脚本创建: Sql代码 1.create global temporary table faretemp 2.( 3.depart char(3), 4.arrive char(3), 5.hops integer, 6.route varchar2(30), 7.price number, 8.constraint faretemp_pk primary key (depart,arrive) 9.); createglobaltemporarytablefaretemp ( departchar(3), arrivechar(3), hopsinteger, routevarchar2(30), pricenumber, constraintfaretemp_pkprimarykey(depart,arrive) ); 一个简单的视图可以在稍微简化这个例子中使用的代码。 视图可以根据fares表中的单个航程计算从faretemp表中的一个路径 到达一下一个航程的数据: Sql代码 1.create or replace view nexthop 2.as 3.select src.depart, 4.dst.arrive, 5.src.hops+1 hops, 6.src.route||','||dst.arrive route, 7.src.price + dst.price price 8.from faretemp src,fares dst 9.where src.arrive = dst.depart 10.and dst.arrive ! = src.depart; 11./ 12.show errors; createorreplaceviewnexthop as selectsrc.depart, dst.arrive, src.hops+1hops, src.route||','||dst.arriveroute, src.price+dst.priceprice fromfaretempsrc,faresdst wheresrc.arrive=dst.depart anddst.arrive! =src.depart; / showerrors; 这个算法相当简单。 首先,使用fares表中的数据填充faretemp表,作为初始的航程。 然后,取到我们刚才插入的所有数据,使用它们建立所有可能的二航程(two-hop)路径。 重复这一过程,直至在两个节点之间创建了新路径。 循环过程将在节点间所有可能的路径都被描述之后退出。 如果我们只对某个开始条件感兴趣,那么我们还可以限制第一次的插入从而减少装载数据的量。 下面是发现路径的代码: Sql代码 1.truncate table faretemp; 2.begin 3.-- initial connections 4.insert into faretemp 5.select depart,arrive,1,depart||','||arrive,price from fares; 6.while sql%rowcoun
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- connect by prior start with语句详解 with 语句 详解