oracle 列转行sql详解

合集下载
  1. 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。

--当期时间贷款时间

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

>KING>JONES

>KING>JONES>SCOTT

>KING>JONES>SCOTT>ADAMS

>KING>JONES>FORD

>KING>JONES>FORD>SMITH

>KING>BLAKE

>KING>BLAKE>ALLEN

>KING>BLAKE>WARD

>KING>BLAKE>MARTIN

>KING>BLAKE>TURNER

>KING>BLAKE>JAMES

>KING>CLARK

>KING>CLARK>MILLER

其实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

相关文档
最新文档