总结笔记之【开发篇sql】

smithpaul

Thank you!

jack198409

呵呵
学习中.....

yellowlee

原帖由 〇〇 于 2010-6-1 07:46 发表
黄鹂如果用宋体有更好的显示效果

只顾自己爽了，我自己的pc屏幕有点大。。。晚上回去改改

mapumama

这个真不错，下来学习了

leng4er

下来学习一下

yellowlee

欢迎指出问题，可以加我qq或者发邮件，有些地方需要改善和修改，目前正在收集建议。。多谢的大家的支持。

njj310

顶起来 啥玩意 看不懂 学习一下

yellowlee

1,层次查询的伪列
Connect_by_iscycle
当前节点（行）的孩子节点（行）也是其父节点（行），则为1，否则为0
Connect_by_isleaf
如果是叶子节点，则输出为1，否则为0
Level
当前节点（行）的层号

用一个例子来直观的看看上述三个伪列的作用：
SQL> select ename,
  2         connect_by_isleaf isleaf,
  3         connect_by_iscycle iscycle,
  4         level lvl,
  5         sys_connect_by_path(ename, '/') path
  6    from scott.emp a
  7   start with empno = 7902
  8  connect by nocycle prior empno = mgr;

ENAME          ISLEAF    ISCYCLE        LVL
---------- ---------- ---------- ----------
PATH
--------------------------------------------------------------------------------
FORD                0          0          1
/FORD

SMITH               1          0          2
/FORD/SMITH


2,层次查询
稍稍修改一下上述查询：
SQL> select ename,
  2         connect_by_isleaf isleaf,
  3         level lvl,
  4         sys_connect_by_path(ename, '/') path
  5    from scott.emp a start with ename = 'KING'
  6  connect by nocycle  prior empno  = mgr  ;

ENAME          ISLEAF        LVL
---------- ---------- ----------
PATH
--------------------------------------------------------------------------------
KING                0          1
/KING

JONES               0          2
/KING/JONES

SCOTT               0          3
/KING/JONES/SCOTT


ENAME          ISLEAF        LVL
---------- ---------- ----------
PATH
--------------------------------------------------------------------------------
ADAMS               1          4
/KING/JONES/SCOTT/ADAMS

FORD                0          3
/KING/JONES/FORD

SMITH               1          4
/KING/JONES/FORD/SMITH


ENAME          ISLEAF        LVL
---------- ---------- ----------
PATH
--------------------------------------------------------------------------------
BLAKE               0          2
/KING/BLAKE

ALLEN               1          3
/KING/BLAKE/ALLEN

WARD                1          3
/KING/BLAKE/WARD


ENAME          ISLEAF        LVL
---------- ---------- ----------
PATH
--------------------------------------------------------------------------------
MARTIN              1          3
/KING/BLAKE/MARTIN

TURNER              1          3
/KING/BLAKE/TURNER

JAMES               1          3
/KING/BLAKE/JAMES


ENAME          ISLEAF        LVL
---------- ---------- ----------
PATH
--------------------------------------------------------------------------------
CLARK               0          2
/KING/CLARK

update              1          3
/KING/CLARK/update


14 rows selected.

SQL> select ename,job,
  2         connect_by_isleaf isleaf,
  3         level lvl,
  4         sys_connect_by_path(ename, '/') path
  5    from scott.emp a start with ename = 'SMITH'
  6  connect by nocycle   empno  = prior mgr order by ename ,lvl ;

ENAME      JOB           ISLEAF        LVL
---------- --------- ---------- ----------
PATH
--------------------------------------------------------------------------------
FORD       ANALYST            0          2
/SMITH/FORD

JONES      MANAGER            0          3
/SMITH/FORD/JONES

KING       PRESIDENT          1          4
/SMITH/FORD/JONES/KING


ENAME      JOB           ISLEAF        LVL
---------- --------- ---------- ----------
PATH
--------------------------------------------------------------------------------
SMITH      CLERK              0          1
/SMITH

上述2个查询均使用sys_connect_by_path，不同是prior的位置不同，前一个是从ename为KING开始，遍历mgr为KING或者间接的mgr为KING的所有路径，直到叶子节点。后面一个是从SMITH开始的所有SMITH的上司的路径，可以看出2者走的路径刚好相反。

修改上述2个查询，再来看看connect_by_root()
SQL> select ename,job,
  2         connect_by_isleaf isleaf,
  3         level lvl,
  4         connect_by_root(ename) root
  5    from scott.emp a start with ename = 'SMITH'
  6  connect by nocycle   empno  = prior mgr order by ename ,lvl ;

ENAME      JOB           ISLEAF        LVL ROOT
---------- --------- ---------- ---------- ----------
FORD       ANALYST            0          2 SMITH
JONES      MANAGER            0          3 SMITH
KING       PRESIDENT          1          4 SMITH
SMITH      CLERK              0          1 SMITH

SQL> select ename,
  2         connect_by_isleaf isleaf,
  3         level lvl,
  4         connect_by_root(ename) root
  5    from scott.emp a start with ename = 'KING'
  6  connect by nocycle  prior empno  = mgr  ;

ENAME          ISLEAF        LVL ROOT
---------- ---------- ---------- ----------
KING                0          1 KING
JONES               0          2 KING
SCOTT               0          3 KING
ADAMS               1          4 KING
FORD                0          3 KING
SMITH               1          4 KING
BLAKE               0          2 KING
ALLEN               1          3 KING
WARD                1          3 KING
MARTIN              1          3 KING
TURNER              1          3 KING

ENAME          ISLEAF        LVL ROOT
---------- ---------- ---------- ----------
JAMES               1          3 KING
CLARK               0          2 KING
update              1          3 KING

14 rows selected.

显然只是给出了root，而不是path。
通常需要获得层次关系，层号，或者路径等问题可以通过oracle提供的层次查询语法和相应的伪列来解决。

chaoxx

楼主是人才，鉴定完毕！

yellowlee

原帖由 chaoxx 于 2010-6-1 21:00 发表
楼主是人才，鉴定完毕！

这。。怎么听着这么熟悉，一般好像不是说人才，是说2个字母的。。