with与树查询的使用

 2010年12月23日 12:55:46

 with 查询的使用

with 查询的作用:就是把一大堆重复用到的SQL语句放在with as 里面，取一个别名，后面的查询就可以用它
这样对于大批量的SQL语句起到一个优化的作用

create table t2(id int);   
create table t3(id int);   
insert into t2 values(1);  
insert into t2 values(2);  
insert into t3 values(3);  
commit;   

with  
 sql1 as (select * from t2),  
 sql2 as (select * from t3)  
  select * from t2  
   union  
  select * from t3;   
 
ERROR at line 3:  
 ORA-32035:不可引用在with子句中定义的查询名

所以，如果定义了sql1和sql2，就得用它们
如下：
with  
 sql1 as (select * from t2),  
 sql2 as (select * from t3)  
  select * from sql1  
   union  
  select * from sql2;   

with子句中的where使用  
with  
     sql1 as (select * from t2),  
     sql2 as (select * from t3)  
     select * from sql1  
     union  
     select * from sql2  
     where id in(2,3);   
输出结果认为1，2，3.

where条件要征对每个select子句
with  
     sql1 as (select * from t2),  
     sql2 as (select * from t3)  
     select * from sql1  
     where id=3
     union  
     select * from sql2  
     where id=3;  

 

树查询的使用    
--层次查询
CREATE TABLE tree_TEST (
ID    NUMBER,
NAME VARCHAR2(100 BYTE),
PID   NUMBER   DEFAULT 0
);

select * from tree_TEST
插入测试数据：
INSERT INTO tree_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO tree_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO tree_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO tree_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO tree_TEST(ID,NAME,PID) VALUES('5','121','2');  

--从根往节点找
select * from tree_TEST start with id = 1 connect by prior id = pid
--从节点往根找
select * from tree_TEST start with id = 5 connect by id = prior pid

--注意树查询时多个条件下，where条件在树结构查询之后
select ... from tablename
where 条件3
start with 条件1
connect by 条件2；