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
se
lect 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
se