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 tSTART WITH t.area_name = 项目所在地 CONNECT BY PRIOR t.area_id = t.area_pidSYS_CONNECT_BY_PATH 学习2008-09-08 10:59SELECT enameFROM scott.emp START WITH ename = KING CONNECT BY PRIOR empno = mgr; 得到结果为：KINGJONESSCOTTADAMSFORDSMITHBLAKEALLENWARDMARTINTURNERJAMES而：SELECT SYS_CONNECT_BY_PATH(ename, ) Path FROM scott.emp START WITH ename = KING CONNECT BY PRIOR empno = mgr;得到结果为：KINGKINGJONESKINGJONESSCOTTKINGJONESSCOTTADAMSKINGJONESFORDKINGJONESFORDSMITHKINGBLAKEKINGBLAKEALLENKINGBLAKEWARDKINGBLAKEMARTINKINGBLAKETURNERKINGBLAKEJAMESKINGCLARKKINGCLARKMILLER其实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_CONNECT_BY_PATH(DEPTNAME,),2)-总公司,浙江分公司,杭州分公司2、行列转换如把一个表的所有列连成一行，用逗号分隔:SQL select max(substr(sys_connect_by_path(column_name,),2)from (select column_name,rownum rn from user_tab_columns where table_name =DEPT)start with rn=1 connect by rn=rownum ;MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,),2)-DEPTNO,DEPTNAME,MGRNO connect by 例子2009-04-21 09:18层次查询子句connect by，用于构造层次结果集的查询。 语法： START WITH condition CONNECT BY NOCYCLE condition 说明： a、START WITH：告诉系统以哪个节点作为根结点开始查找并构造结果集，该节点即为返回记录中的最高节点。 b、当分层查询中存在上下层互为父子节点的情况时，会返回ORA-01436错误。此时，需要在connect by后面加上NOCYCLE关键字。同时，可用connect_by_iscycle伪列定位出存在互为父子循环的具体节点。connect_by_iscycle必须要跟关键字NOCYCLE结合起来使用，用法见示例2。 用法举例： 示例1：显示所有地名关系结构。 SQL select * from t; AREA_ID AREA_NAME MGR_ID - - - 86 中国 01 北京 86 02 福建 86 0101 海淀区 01 0102 朝阳区 01 0103 东城区 01 0104 西城区 01 0201 厦门 02 0202 福州 02 020101 湖里 0201 020102 思明 0201 010401 复兴门 0104 010402 西单 0104 已选择13行。 SQL SQL set pagesize 50 SQL col AreaName for a12 SQL col Root for a10 SQL col Path for a24 SQL SQL select rpad( , 2*(level-1), ) | area_name AreaName, 2 connect_by_root area_name Root, 3 connect_by_isleaf IsLeaf, 4 level , 5 SYS_CONNECT_BY_PATH(area_name, /) Path 6 from t 7 start with mgr_id is null 8 connect by prior area_id = mgr_id; AreaName Root IsLeaf LEVEL Path - - - - - 中国 中国 0 1 /中国 北京 中国 0 2 /中国/北京 海淀区 中国 1 3 /中国/北京/海淀区 朝阳区 中国 1 3 /中国/北京/朝阳区 东城区 中国 1 3 /中国/北京/东城区 西城区 中国 0 3 /中国/北京/西城区 复兴门 中国 1 4 /中国/北京/西城区/复兴门 西单 中国 1 4 /中国/北京/西城区/西单 福建 中国 0 2 /中国/福建 厦门 中国 0 3 /中国/福建/厦门 湖里 中国 1 4 /中国/福建/厦门/湖里 思明 中国 1 4 /中国/福建/厦门/思明 福州 中国 1 3 /中国/福建/福州 已选择13行。 说明： a、prior：是单一操作符，放在列名的前面，等号左右均可； 放在父 ID 就是 寻找 祖先节点 ，放到本身 ID就是寻找 子节点 ；b、connect_by_root：是单一操作符，返回当前层的最顶层节点； c、connect_by_isleaf：是伪列，判断当前层是否为叶子节点，1代表是，0代表否； d、level：是伪列，显示当前节点层所处的层数； e、SYS_CONNECT_BY_PATH：是函数，显示当前层的详细路径。 示例2：找出人事部门中存在跟其他部门互为管理者的人员名单。 SQL select * from t2; EMP DEPT MGR - - - 刘涛 总裁办 李飞 总裁办 刘涛 张强 总裁办 刘涛 王鹏 人事 李飞 李华 人事 李飞 张强 人事 李飞 李飞 行政 张强 吴华 行政 张强 已选择8行。 SQL SQL col emp for a12 SQL select rpad( , 2*(level-1), ) | emp emp 2 from t2 3 start with dept =人事 4 connect by prior emp = mgr; ERROR: ORA-01436: 用户数据中的 CONNECT BY 循环 未选定行 说明：张强和李飞互为管理者，因此，要用nocycle，如下所示： SQL select rpad( , 2*(level-1), ) | emp emp 2 from t2 3 start with dept =人事 4 connect by nocycle prior emp = mgr; emp - 王鹏 李华 张强 李飞 王鹏 李华 吴华 已选择7行。 SQL SQL select rpad( , 2*(level-1), ) | emp emp, 2 connect_by_iscycle IsCycle 3 from t2 4 start with dept =人事 5 connect by prior emp = mgr; connect by prior emp = mgr * 第 5 行出现错误: ORA-30930: CONNECT_BY_ISCYCLE 伪列要求 NOCYCLE 关键字 说明：在用connect_by_iscycle定位节点时，也要用nocycle关键字，如下所示： SQL SQL select rpad( , 2*(level-1), ) | emp emp, 2 connect_by_iscycle IsCycle 3 from t2 4 start with dept =人事 5 connect by nocycle prior emp = mgr;emp IsCycle - - 王鹏 0 李华 0 张强 0 李飞 1 王鹏 0 李华 0 吴华 0 已选择7行。 SQL 示例3：仅显示第二层（即level=2）省市名称。 SQL select rpad( , 2*(level-1), ) | area_name AreaName 2 from t 3 where level = 2 4 start with mgr_id is null 5 connect by prior area_id = mgr_id; AreaName - 北京 福建 示例4：用connect by构造序列。 SQL SQL select rownum rn 2 from dual 3 connect by rownum select rownum*2 -1 rn 2 from dual 3 connect by rownum=10; RN - 1 3 5 7 9 11 13 15 17 19 已选择10行。 - 附：建表语句 create table t (area_id varchar2(6), area_name varchar2(10), mgr_id varchar2(6); insert into t values(86, 中国, null); insert into t values(01, 北京, 86); insert into t values(02, 福建, 86); insert into t values(0101, 海淀区, 01); insert into t values(0102, 朝阳区, 01); insert into t values(0103, 东城区, 01); insert into t values(0104, 西城区, 01); insert into t values(0201, 厦门, 02); insert into t values(0202, 福州, 02); insert into t values(020101, 湖里, 0201); insert into t values(020102, 思明, 0201); insert into t values(010401, 复兴门, 0104); insert into t values(010402, 西单, 0104); commit; create table t2 (emp varchar2(10), dept varchar2(6), mgr varchar2(10);