[层次结构|树状结构] oracle中用start with...connect by prior子句实现递归查询
来源:互联网 发布:咫尺网络西安 编辑:程序博客网 时间:2024/04/30 14:39
oracle 家族树语法:select column from table_name start with column=valueconnect by prior 父主键=子主键
--自顶向下查询,prior表示上一条记录,比如 connect by prior id=parentid就是说上一条记录的ID是本条记录的parentid,即本记录的父亲是上一条记录。
以ORACLE中的EMP表为例[例]从顶到底列出各雇员的信息SQL> select lpad(' ',4*(level-1))||ename name,empno,mgr from emp start with mgr is null connect by prior empno=mgr; NAME EMPNO MGR--------- --------- ---------KING 7839 JONES 7566 7839 SCOTT 7788 7566 ADAMS 7876 7788
■ In a hierarchical query, one expression in condition must be qualified with thePRIOR operator to refer to the parent row. For example,... PRIOR expr = expror... expr = PRIOR expr
LEVEL Pseudocolumn
For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for
a root row, 2 for a child of a root, and so on. A root row is the highest row within an
inverted tree. A child row is any nonroot row. A parent row is any row that has
children. A leaf row is any row without children.
See Also: "Hierarchical Queries" on page 9-3 for information on
hierarchical queries in general and "IN Condition" on page 7-23 for
restrictions on using the LEVEL pseudocolumn
If the CONNECT BY condition is compound, then only one condition requires thePRIOR operator, although you can have multiple PRIOR conditions. For example:CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...CONNECT BY PRIOR employee_id = manager_id andPRIOR account_mgr_id = customer_id ...
START WITH CONNECT BY PRIOR这个语法主要用于查询数据包中的树型结构关系。先看下原始数据时怎么样的吧!
表中第一行1001是1002的父节点,而第二行1002又是1003的父节点,如此循环。如题:要求给出其中一个数字能找出其最终的根节点!应该要怎么样实现呢?请看如下sql语句,这里我就拿1008为例。
图中第一行第一个num1就是根节点了。再来分析下sql语句:
select num1,num2,level
from carol_tmp
start with num2=1008
connect by num2=prior num1 order by level desc;
prior放的左右位置决定了检索是自底向上还是自顶向下.很明显以上的sql选择了自底向上,所以最终得到了根节点。
这次要求获得最小的叶节点,看下图:
第一行的num1就是最小的叶节点了,在注意看下sql代码和上面的代码有什么不同之处:
select num1,num2,level
from carol_tmp
start with num2=1008
connect by prior num2= num1 order by level desc;
这次prior和num2放在了一起,他意思就是从num2开始寻找其下面最小的叶节点。
为什么prior和谁在一起比较重要呢?比如prior和 num2同在等号的一边,这个时候要看num2和num1的关系。若num2一直是parent,那么这个sql找的就是根节点。
在oracle中的select语句可以用start with...connect by prior子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是:
//如果只用connect by 而不加 prior 查找的将是 level 为1的一级。
select ... from tablename start with cond1
connect by prior cond2
where cond3;
这里的where是不能加的,我一加一个where就报 sql命令未正确结束的错误。如果有人可以加上去使用,那也请高手我下吧。本人先谢谢了。
不过可以先写 where 然后使用 connect by prior 例如:select * from Sysfunction where nodetype='4' start with parentid ='123'(值) connect by prior functionid=parentid order by level(关键字:级别),parentid, funorder;
简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段:
id,parentid那么通过表示每一条记录的parent是谁,就可以形成一个树状结构。
用上述语法的查询可以取得这棵树的所有记录。
其中cond1 是根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树。
cond2是连接条件,其中用prior表示上一条记录,比如 connect by prior id=parentid就是说上一条记录的ID是本条记录的parentid,即本记录的父亲是上一条记录。
cond3是过滤条件,用于对返回的所有记录进行过滤。
对于oracle进行简单树查询(递归查询)
deptid paredeptid name
number number char (40 Byte)
部门id 父部门id(所属部门id) 部门名称
通过子节点向根节点追朔.
复习一下:start with ...connect by 的用法, start with 后面所跟的就是就是递归的种子。
递归的种子也就是递归开始的地方 connect by 后面的"prior" 如果缺省:则只能查询到符合条件的起始行,并不进行递归查询;
connect by prior 后面所放的字段是有关系的,它指明了查询的方向。
下面看下几个例子:
create table automobiles(
part_id number(5)
constraint pk_auto_part_id primary key,
parent_id number(5)
constraint fk_auto_ppid_ references automobiles(part_id),
part_cname varchar2(30) not null,
part_ename varchar2(30) not null,
mp_cost number(9,2),
desribe varchar2(20)
);
--插入数据
insert into automobiles values( 1,null,'汽车','mobile',84321.99,'Assembly');
insert into automobiles values( 2,1,'车身','bodywork',19892.99,'Manufacture');
insert into automobiles values( 3,1,'发送机','engine',42128,'Purchase');
insert into automobiles values( 4,1,'附件','attached',15212,'Assembly');
insert into automobiles values( 5,2,'保险杠','bumper',4812.95,'Purchase');
insert into automobiles values( 6,2,'底盘','chassis',12795.11,'Manufacture');
insert into automobiles values( 7,2,'行李箱','Boot',812.11,'Manufacture');
--分层sql脚本语句练习
select level,part_id,parent_id,part_cname,part_ename,mp_cost,desribe
from automobiles
start with part_id=1
connect by prior part_id=parent_id
order by level;
--缩进显示
select level,
lpad(' ',2*level-1)||part_cname||' '||part_ename as partName
from automobiles
start with part_id=1
connect by prior part_id=parent_id
order by level;
--使用子查询 当然也可以在from中加入条件来达到子查询的效果
select level,lpad(' ',2*level-1)||part_cname||' '||part_ename as partName from automobiles
start with part_id=(select part_id from automobiles where part_cname like '%轴%')
connect by prior part_id=parent_id
order by level;
--自底向上的遍历
select level,lpad(' ',2*level-1)||part_cname||' '||part_ename as partName
from automobiles
start with part_id=(select part_id from automobiles where part_cname like '%轴%')
connect by prior parent_id=part_id
order by level;
--删除指定的节点
select level,lpad(' ',2*level-1)||part_cname||' '||part_ename as partName from automobiles
where part_cname <> '底盘'
start with part_id=1
connect by prior parent_id=part_id
order by level;
--删除分支
select level,lpad(' ',2*level-1)||part_cname||' '||part_ename as partName from automobiles
start with part_id=1
connect by prior parent_id=part_id and part_cname <> '底盘'
order by level;
*************************************** TRY ******************************************************************************
select rownum from dual connect by rownum <= 3; 1=1
SELECT LEVEL,(TO_DATE('20150101', 'yyyy-mm-dd') + LEVEL - 1) CUR_DATE FROM DUAL
CONNECT BY LEVEL <= SYSDATE - TO_DATE('20150101', 'yyyy-mm-dd') + 1
select SYSDATE - TO_DATE('20150101', 'yyyy-mm-dd')+1 from dual;
select * from dual
select TO_DATE('20150101', 'yyyy-mm-dd')+32.9 from dual
with x as
( select 'aa' chr from dual
union all
select 'bb' chr from dual)
select level ,chr,lpad( ' ' ,( level - 1 )* 5 , '-' )||chr other from x connect by level <= 3
with t as (select level from dual connect by level<=3)
select rownum from t connect by rownum<=3
with t as (select level l from dual connect by level<=3)
select rownum,l,LEVEL from t connect by rownum<=1
with t as (select level l from dual connect by level<=3)
select rownum,l,LEVEL from t connect by rownum=2
select distinct ct from (
select d.cal_year,count(*) as ct from td_day d /*where d.calendar_date is null*/ group by d.cal_year ) --365,366
select distinct ct from (
select w.cal_year,count(*) as ct from w_day_d w group by w.cal_year )--372
- [层次结构|树状结构] oracle中用start with...connect by prior子句实现递归查询
- Oracle start with......connect by prior......子句实现递归查询
- Oracle start with.connect by prior子句实现递归查询
- Oracle start with.connect by prior子句实现递归查询
- Oracle start with.connect by prior子句实现递归查询
- Oracle start with.connect by prior子句实现递归查询
- Oracle start with.connect by prior子句实现递归查询
- oracle中用START WITH...CONNECT BY PRIOR子句实现递归查询
- oracle中用START WITH...CONNECT BY PRIOR子句实现递归查询
- oracle中用START WITH...CONNECT BY PRIOR子句实现递归查询
- oracle中用START WITH...CONNECT BY PRIOR子句实现递归查询
- oracle中用start with...connect by prior子句实现递归查询[例子不错]
- oracle关键字 start with、、、 connect by prior 子句递归查询
- 用START WITH...CONNECT BY PRIOR子句实现递归查询
- START WITH CONNECT BY PRIOR子句实现递归查询
- START WITH CONNECT BY PRIOR子句实现递归查询
- start with connect by prior子句实现递归查询
- START WITH CONNECT BY PRIOR子句实现递归查询
- hdu(5360)——Hiking
- UVa 503 - Parallelepiped walk
- 使用Jmeter测试web应用
- oracle 11g 密码过期|修改密码
- scala Case Class和模式匹配(1):简单的示例
- [层次结构|树状结构] oracle中用start with...connect by prior子句实现递归查询
- Android之安全机制
- MyEclipse注册
- 微信语音输入不仅仅是懒癌患者的福音
- 文章标题
- 详解Objective-C中委托和协议
- 表格视图UITableView
- jquery 插件开发 $.extend $.fn.extend 全局对象 全局函数
- Android分辨率适配layout布局的问题