内联视图、标量子查询、WITH子查询示例

with t as (select * from scott.emp where 1=1)

select * from t where empno='';

select * from (

select sal as salary,comm as commission from scott.emp ) x

where salary <5000;

www.zzzyk.com

-- 内联视图

内联视图通常是指一个SQL查询的结果作为另一个查询的数据源, 一般在 From字句后面

select p.pname,c1_sum1,c2_sum2

from p,

(select id,sum(q1) c1_sum1 from s1 group by id) s1,

(select id,sum(q2) c2_sum2 from s2 group by id) s2

where p.id=s1.id

and p.id=s2.id

-- 标量子查询

select

p.pname,

(select sum(q1) c1_sum1 from s1 where s1.id=p.id) c1_sum1,

(select sum(q2) c1_sum1 from s2 where s2.id=p.id) c2_sum2

from p

-- with子查询分解

主要是方便理解，增加可读性

with c1_vw as (select id,sum(q1) sum1 from s1 group by id),

c2_vw as (select id,sum(q2) sum2 from s2 group by id),

c1_c2 as (select t1.id,t1.sum1,t2.sum2 from c1_vw t1,c2_vw t2 where t1.id=t2.id)

select p.pname,sum1,sum2

from p,c1_c2

where p.id=c1_c2.id