Oracle Database 11g SQL开发指南学习笔记：高级查询

1、集合操作
[sql] 
--1.intersect  
with t  
as  
(  
  select 1 as v,'abc' as vv from dual  
  union all  
  select 1 ,'abc' from dual  
  union all  
  select 2,'def' from dual  
),  
  
tt  
as  
(  
  select 1 as v,'abc' as vv from dual  
  union all  
  select 1,'abc' from dual  
  union all  
  select 3,'def' from dual  
)  
  
/*  
只返回一条记录，说明求交集后，会去重。  
从执行计划中看，会首先会对第一个表有一个sort操作，选项是unique，也就是去重。  
然后第二个表，也是一样。  
也就是先把每个表的记录进行排序去重，然后再求交集  
         V VV  
---------- ---  
         1 abc   
  
*/  
select v,vv from t  
intersect   
select v,vv from tt  
  
  
--2.minus  
with t  
as  
(  
  select 1 as v,'abc' as vv from dual  
  union all  
  select 1 ,'abc' from dual  
  union all  
  select 2,'def' from dual  
),  
  
tt  
as  
(  
  select 1 as v,'abc' as vv from dual  
  union all  
  select 3,'def' from dual  
)  
  
/*  
从执行计划中看，会首先会对第一个表有一个sort操作，选项是unique，也就是去重。  
然后第二个表，也是一样。  
然后第一个结果集减去第二个结果集，所以只会返回一条记录。  
  
         V VV  
---------- ---  
         2 def   
*/  
select v,vv from t   
minus  
select v,vv from tt  

2、decode函数、translate函数

[sql] 
select v,  
         
       --decode函数类似于case when，可有多个参数  
       decode(v,  
              1,1,  
              2,2,  
              3,3  
             ),  
         
       --translate函数类似于replace，不过是加强版，按照替换规则进行替换         
       translate(vv,            --要替换的字符串  
                 'abcdefghi',   --被替换的字符  
                 '123456789')   --替换为的字符  
         
from    
(  
  select 1 as v,'abc' as vv from dual  
  union all  
  select 2 as v,'def' as vv from dual  
  union all  
  select 3 as v,'ghi' as vv from dual  
)  

3、层次化查询

[sql] 
--1.层次查询：自顶向下、自底向上  
--自顶向下  
with t  
as  
(  
  select 1 as v,null parent_v,'01' vv from dual  
  union all  
    
  select 2 as v,1,'02' as vv from dual  
  union all  
  select 3 as v,2,'03' as vv from dual  
  union all  
  select 4 as v,2,'04' as vv from dual  
  union all  
    
  select 5 as v,1,'05' as vv from dual  
  union all  
  select 6 as v,5,'06' as vv from dual  
  union all  
  select 7 as v,6,'07' as vv from dual  
  union all    
  select 8 as v,5,'08' as vv from dual  
  union all  
  select 9 as v,8,'09' as vv from dual  
  union all  
    
  select 10 as v,1,'10' as vv from dual  
  union all    
  select 11 as v,10,'11' as vv from dual  
  union all  
  select 12 as v,10,'12' as vv from dual  
  union all  
  select 13 as v,10,'13' as vv from dual  
)  
  
/*  
LPAD('',2*LEVEL-1)||VV          
------------------------------------  
 01    
   02  
     03
     04
   05  
     06
       07                         
     08
       09                         
   10  
     11
     12
     13
  
*/  
select lpad(' ',2 * level - 1) || vv  
from t  
start with v = 1  
connect by prior v = parent_v;  
  
  
--自底向上  
with t  
as  
(  
  select 1 as v,null parent_v,'01' vv from dual  
  union all  
    
  select 2 as v,1,'02' as vv from dual  
  union all  
  select 3 as v,2,'03' as vv from dual  
  union all  
  select 4 as v,2,'04' as vv from dual  
  union all  
    
  select 5 as v,1,'05' as vv from dual  
  union all  
  select 6 as v,5,'06' as vv from dual  
  union all  
  select 7 as v,6,'07' as vv from dual  
  union all    
  select 8 as v,5,'08' as vv from dual  
  union all  
  select 9 as v,8,'09' as vv from dual  
  union all  
    
  select 10 as v,1,'10' as vv from dual  
  union all    
  select 11 as v,10,'11' as vv from dual  
  union all  
  select 12 as v,10,'12' as vv from dual  
  union all  
  select 13 as v,10,'13' as vv from dual  
)  
  
/*  
LPAD('',2*LEVEL-1)||VV          
----------------------------------------------------------------  
09    
   08  
     05
       01    
*/  
--注意，level是伪劣，第一层恢复返回1  
select lpad(' ',2 * level - 1) || vv  
from t  
start with v = 9  
connect by  v = prior parent_v; --connect by prior parent_v = v 效果是一样的  
  
  
--2.过滤  
--通过start with的条件，从非根结点开始遍历  
with t  
as  
(  
  select 1 as v,null parent_v,'01' vv from dual  
  union all  
    
  select 2 as v,1,'02' as vv from dual  
  union all  
  select 3 as v,2,'03' as vv from dual  
  union all  
  select 4 as v,2,'04' as vv from dual  
  union all  
    
  select 5 as v,1,'05' as vv from dual  
  union all  
  select 6 as v,5,'06' as vv from dual  
  union all  
  select 7 as v,6,'07' as vv from dual  
  union all    
  select 8 as v,5,'08' as vv from dual  
  union all  
  select 9 as v,8,'09' as vv from dual  
  union all  
    
  select 10 as v,1,'10' as vv from dual  
  union all    
  select 11 as v,10,'11' as vv from dual  
  union all  
  select 12 as v,10,'12' as vv from dual  
  union all  
  select 13 as v,10,'13' as vv from dual  
)  
  
select lpad(' ',2 * level - 1) || vv  
from t  
start with v = 5    --这个查询条件只是过滤整个查询的起点，也就是从哪个节点开始遍历  
                    --如果没有这个过滤条件，那么会尝试从每个值  
connect by prior v = parent_v;  
  
  
--在start with的条件中使用子查询  
with t  
as  
(  
  select 1 as v,null parent_v,'01' vv from dual  
  union all  
    
  select 2 as v,1,'02' as vv from dual  
  union all  
  select 3 as v,2,'03' as vv from dual  
  union all  
  select 4 as v,2,'04' as vv from dual  
  union all  
    
  select 5 as v,1,'05' as vv from dual  
  union all  
  select 6 as v,5,'06' as vv from dual  
  union all  
  select 7 as v,6,'07' as vv from dual  
  union all    
  select 8 as v,5,'08' as vv from dual  
  union all  
  select 9 as v,8,'09' as vv from dual  
  union all  
    
  select 10 as v,1,'10' as vv from dual  
  union all    
  select 11 as v,10,'11' as vv from dual  
  union all  
  select 12 as v,10,'12' as vv from dual  
  union all  
  select 13 as v,10,'13' as vv from dual  
)  
  
/*  
LPAD('',2*LEVEL-1)||VV 
                          
-----------------------------  
 05                       
   06                         
     07                       
   08               
     09   
*/  
select lpad(' ', 2 * level - 1) || vv  
from t  
start with v = (select v from t where vv = '05')  
connect by prior v = parent_v;  
  
  
--通过connect by条件删除分支  
with t  
as  
(  
  select 1 as v,null parent_v,'01' vv from dual  
  union all  
    
  select 2 as v,1,'02' as vv from dual  
  union all  
  select 3 as v,2,'03' as vv from dual  
  union all  
  select 4 as v,2,'04' as vv from dual  
  union all  
    
  select 5 as v,1,'05' as vv from dual  
  union all  
  select 6 as v,5,'06' as vv from dual  
  union all  
  select 7 as v,6,'07' as vv from dual  
  union all    
  select 8 as v,5,'08' as vv from dual  
  union all  
  select 9 as v,8,'09' as vv from dual  
  union all  
    
  select 10 as v,1,'10' as vv from dual  
  union all    
  select 11 as v,10,'11' as vv from dual  
  union all  
  select 12 as v,10,'12' as vv from dual  
  union all  
  select 13 as v,10,'13' as vv from dual  
)  
  
/*  
LPAD('',2*LEVEL-1)||VV          
-------