oracle中的contact,[数据库]Oracle Contact By的使用

[数据库]Oracle Contact By的使用

0

2015-01-08 11:00:27

1.概述

Oracle “CONNECT BY”是层次查询子句，一般用于树状或者层次结果集的查询

2.使用方式

2.1.通过Connect by 生成序列Oracle 构造一个月份的天数select to_date('200809','yyyymm')+(rownum-1) s_date from dual connect by rownum<=last_day(to_date('200809','yyyymm')) - to_date('200809','yyyymm') + 1

生成1-10的一个序列SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10

2.2实现树状查询结果create table DEP( DEPID number(10) not null, DEPNAME varchar2(256), UPPERDEPID number(10))---------------------------------------------------------------------INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (0, '总经办', null);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (1, '开发部', 0);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (2, '测试部', 0);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (3, 'Sever开发部', 1);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (4, 'Client开发部', 1);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (5, 'TA测试部', 2);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (6, '项目测试部', 2);---------------------------------------------------------------------SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",CONNECT_BY_ROOT DEPNAME "ROOT",CONNECT_BY_ISLEAF "ISLEAF",LEVEL ,SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH"FROM DEPSTART WITH UPPERDEPID IS NULLCONNECT BY PRIOR DEPID = UPPERDEPID;

DEMO下载

转载请保留本文网址：http://www.shaoqun.com/a/108324.html

*特别声明：以上内容来自于网络收集，著作权属原作者所有，如有侵权，请联系我们：admin@shaoqun.com。

oracle

0