ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle中with的用法

Oracle中with的用法

原创 Linux操作系统 作者:regonly1 时间:2009-05-06 12:01:27 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12932950/viewspace-594291/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-05-10

  • 博文量
    257
  • 访问量
    1038114