现在的位置: 首页 > 数据库 > 正文

Oracle层次化查询及connect by生成序列

2014年11月30日 数据库 ⁄ 共 1982字 暂无评论 ⁄ 阅读 2,061 次

Oracle层次化查询多用于构建层级关系树,如在人事关系中的行政级别、在地方关系中的管辖级别等。主要借助关键字start with和connect by prior实现的,其语法如下:

SELECT [LEVEL],column,expression, ...

FROM table

[WHERE conditions]

[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

  • level是伪列,代表树的层级,根节点level为1,子节点为2等。
  • from后面只能是一个表或一个视图。
  • where条件可以限制查询返回的行,但不影响层次关系,属于将节点截断,但是这个被截断的节点的下层child不受影响。注意,彻底剪枝条件应放在connect by(connect by之后也可跟过滤条件,它将该条件节点后的所有子孙后代一并去除不显示,如:connect by prior employee_id=manager_id and employee_id>10),单点剪掉条件应放在where子句(入层后不输出)。因为connect by的优先级要高于where,也就是sql引擎先执行connect by。
  • start_condition定义层次化查询的起点,如employee_id=1。
  • prior_condition定义父行和子行之间的关系,如父子关系定义为employee_id=manager_id,表示父节点employee_id和子节点manager_id之间存在关系。如果不加PRIOR关键字则不会进行递归,只是相当于一个过滤条件,只能得到根节点。另外,该关键字可放在前列前,也可放在后列前,放在哪列前哪列就是根节点。另外,connect by还可用于生成简易序列,可见最后。

举例:

select level,employee_id,manager_id,name

from employees

start with employee_id = 1

connect by prior employee_id = manager_id

order by level;

获取树层次数:

select count(distinct level)

from employees

start with employee_id = 1

connect by prior employee_id = manager_id;

获取每层人数:

select level,count(name)

from employees

start with employee_id = 1

connect by prior employee_id = manager_id

group by level;

格式化输出层次化结果:

通过在姓名前增加空格,有利于查看用户所在层次,可利用lpad函数,如:

select level,lpad(name,2 * (level – 1),’ ’) as name

from employees

start with employee_id = 1

connect by prior employee_id = manager_id;

由下向上遍历:

层次化查询一般从上向下遍历成树,然而,也可以从某个子节点开始从下向上遍历,这样将得到一条线,只需要把连接条件优先顺序换一下即可,如:

select level,employee_id,manager_id,name

from employees

start with employee_id = 10

connect by prior manager_id = employee_id

order by level;

排除指点节点:

select level,employee_id,manager_id,name

from employees

start with employee_id = 1

where name <> ‘SCOTT’

connect by prior employee_id = manager_id

order by level;

排除指点分支:

select level,employee_id,manager_id,name

from employees

start with employee_id = 1

connect by prior employee_id = manager_id

and name <> ‘SCOTT’

order by level;

connect by生成序列:

如生成一个1到10的序列:

SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10;

其原理在于:省略start with则以所以点为根节点,而dual表只有一行所有只有一个节点,而connect by则对所有输入内容进行遍历。

上面的方法受制于rownum伪列的限制,想得到指定始尾的序列我们也可以借助level伪列,如:select level from dual where level >= 5 connect by level <= 10;

» 声明:本站文章源于个人经验总结或书籍、互联网转载,内容仅用于个人学习,请勿转载,否则后果自负!

给我留言

留言无头像?