oracle --with by/connect by用法小计

今天无意间想到了一组连续的数(不一定非得是表中的数据) 需按某个规则(比如除以4后换行)被排成多少列,百思不得其解, 最后终于在论坛上找到了答案,如下：

eg: 1..100 要按如下方式排序 1 2 3 4 ... 97 98 99 100

解决方法：

with t as (select rownum-1 r from dual connect by rownum<25 select from t ---------- ---------- ---------- ---------- as

sql> create table t2(id int); SQL> create table t3(id int);

SQL> insert into t2 values(1);

SQL> insert into t2 values(2);

SQL> insert into t3 values(3);

SQL> with 2 sql1 as (select * from t2), 3 sql2 as (select * from t3) 4 select * from sql1 5 union 6 select * from sql2;

结果如下： ID ---------- 1 2 3

需要注意的地方是,你在这里定义了sql1和sql2,就必须使用它们, 否则会报错:

SQL> with 2 sql1 as (select * from t2), 3 sql2 as (select * from t3) 4 select * from t2 5 union 6 select * from t3; sql2 as (select * from t3)

* 第 3 行出现错误: ORA-32035: 不可引用在 WITH 子句中定义的查询名

另外,对每个select语句加where进行限制的时候,where的作用仅限于当前的select语句,比如说

SQL> with 2 sql1 as (select * from t2), 3 sql2 as (select * from t3) 4 select * from sql1 5 union 6 select * from sql2 7 where id in (2,3);

它的输出结果还是同上,必须对单个select做限制才能保证输出自己想要的结果. connect by 用法：很明显这里connect by的用法就是生成一组有序数,数的值在1-25之间(rownum默认起点为1) 这里注意connect by的使用顺序：

select rownum from dual connect by rownum <25 select rownum from dual where rownum connect by>

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27025753/viewspace-1058517/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27025753/viewspace-1058517/