oraclewith嵌套with_Oracle中with的用法

Oracle中有个with字句，用于构建复杂的查询语句。

语句结构：

with subquery_name1 as (subquery_body1),

subquery_name2 as (subquery_body2)

...

select * from subquery_name1 a, subquery_name2 b

where a.col = b.col

....

以下是实例：

with

subinfo as (select  /*+materialize */ssp.subscriber_id, ssp.subscriber_date

from ow_subscription ssp

where ssp.inactive_date > ssp.active_date

and ssp.active_date < date '2008-12-01'

and ssp.active_date >= date '2008-11-01'

and ssp.product_id = 494092609),

iptvcnt as (select /*+materialize */s.subscriber_id, count(*)  cnt

from ow_iptv_bill ib, subinfo s

where ib.starttime >= date '2008-11-01'

and ib.starttime < date '2008-12-01'

and ib.subscriberid = s.subscriber_id

group by s.subscriber_id),

iptvtime as (select /*+materialize */s.subscriber_id, sum(de.totaltime) totaltime

from ow_iptvbill_detail de, ow_iptv_bill ib, subinfo s

where to_char(ib.id) = de.transactionkey

and de.startdate < date '2008-12-01'

and de.startdate >= date '2008-11-01'

and ib.starttime >= date '2008-11-01'

and ib.starttime < date '2008-12-01'

and ib.subscriberid = s.subscriber_id

group by s.subscriber_id)

select sub.subscriber_id, sub.name, s.subscriber_date, a.cnt, b.totaltime

from ow_subscriber sub,  subinfo s, iptvcnt a, iptvtime b

where b.subscriber_id = s.subscriber_id

and a.subscriber_id = s.subscriber_id

and sub.subscriber_id = s.subscriber_id