oraClE-列转行sql详解.doc

--当期时间 贷款时间 SELECT DK_ID, max(substr(activeDate, 2)) activeDate FROM (SELECT DK_ID, sys_connect_by_path(activeDate, ,) activeDate FROM (SELECT DK_ID, activeDate, DK_ID || rn rchild, DK_ID || (rn - 1) rfather FROM (SELECT TEMP.DK_ID, --查询项目所在地树形结构全名 SELECT t.area_id, substr(sys_connect_by_path(t.area_name, -), 2) as allname , connect_by_root t.area_name as root, --是单一操作符，返回当前层的最顶层节点 connect_by_isleaf as IsLeaf, --是伪列，判断当前层是否为叶子节点，1代表是，0代表否 level as lel --是伪列，显示当前节点层所处的层数 FROM dk_project_area_info t START WITH t.area_name = 项目所在地 CONNECT BY PRIOR t.area_id = t.area_pid SYS_CONNECT_BY_PATH 学习2008-09-08 10:59SELECT ename FROM scott.emp START WITH ename = KING CONNECT BY PRIOR empno = mgr; 得到结果为：KING JONES SCOTT ADAMS FORD SMITH BLAKE ALLEN WARD MARTIN TURNER JAMES 而：SELECT SYS_CONNECT_BY_PATH(ename, ) Path FROM scott.emp START WITH ename = KING CONNECT BY PRIOR empno = mgr; 得到结果为：KING KINGJONES KINGJONESSCOTT KINGJONESSCOTTADAMS KINGJONESFORD KINGJONESFORDSMITH KINGBLAKE KINGBLAKEALLEN KINGBLAKEWARD KINGBLAKEMARTIN KINGBLAKETURNER KINGBLAKEJAMES KINGCLARK KINGCLARKMILLER 其实SYS_CONNECT_BY_PATH这个函数是oracle9i才新提出来的！ 它一定要和connect by子句合用！ 第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！ START WITH 代表你要开始遍历的的节点， CONNECT BY PRIOR 是标示父子关系的对应！ 如下例子：select max( substr( sys_connect_by_path(column_name,,) ,2) ) from (select column_name,rownum rn from user_tab_columns where table_name =AA_TEST) start with rn=1 connect by rn=rownum ; 是将列用,进行分割成为一行，然后将首个，去掉，只取取最大的那个数据。 －－－－－－－－－ 下面是别人的例子： 1、带层次关系 SQL create table dept(deptno number,deptname varchar2(20),mgrno number); Table created. SQL insert into dept values(1,总公司,null); 1 row created. SQL insert into dept values(2,浙江分公司,1); 1 row created. SQL insert into dept values(3,杭州分公司,2); 1 row created. SQL commit; Commit complete. SQL select max(substr(sys_connect_by_path(deptname,,),2)) from dept connect by prior deptno=mgrno; MAX(SUBSTR(SYS_CONNE