ORACLE with语句

用一个sql语句完成一下问题：
A,B,C三人的年龄相乘为36；相加等于所有在场的人的人数；年龄大的人的宠物仓鼠的有个腿是木头的，试着给出这三人的年龄。
解决方案：
需要知道用于查询分解的基本格式，考虑类似下面的表达式

    with
alias1 as (subQuery1)
alias2 as(subQuery2)
        ……
   select
        ……
；

首先，注意带三人年龄的乘积是36，可设年龄都是整数，因此就必须创建一个1~36的范围内，——就需要一个36行的表，每一个表对应一个可能的年龄：

    with age_list as
    {
    select rowNum as age from dual where rowNum<=36;
    }，

是三个人，需要创建三个副本，同时年龄乘积是36——之后还要把这3个年龄相加，下面同时进行。注意前面的SQL结尾使用是一个逗号，下面的部分就简单地以一个新的别名开始（不再重复with）：

     product_check as
      {
           select
                      age1.age as youngest,
                      age2.age as middle,
                      age3.age as oldest
                      age1.age+age2.age+age3.age as sumed
           from age_list age1,age_list2 age2,age_list age3
           where age1.age<=age2.age and age2.age<=age3.age   //保证每种组合值出现一次
                      and age1.age*age2.age*age3.age=36
     }

得出的结果是
[list]
[*] Y M O S
[*]——————————————————————————
[*] 1 1 36 38
[*] 1 2 18 21
[*] 1 3 12 16
[*] 1 4 9 14
[*] 1 6 6 13
[*] 2 2 9 13
[*] 2 3 6 11
[*] 3 3 4 10
[/list]
现在 年龄之和等于一个数，知道了现场人数仍不能确定，——说明查询集合的和至少有两行的值是一样的。
缩小输出范围

    summed_checked as
     {
           select youngest, middle, oldest ,sumed
           from
                { select youngest,middle,oldest,sumed,count (*) over (partition by summed) cnt
                    from     product_chect
                }
           where cnt>=2
   }

输出结果：
[list]
[*] Y M O S
[*]————————————————————————————
[*] 1 6 6 13
[*] 2 2 9 13
[/list]
接着“年龄大的人的宠物仓鼠有个腿是木质的”，表明年龄大的人的年龄比中间那个要大。
所以

    select yongest,middle,oldest from sumed_checked
where oldest>middle

得出结果：
[list]
[*] Y M O S
[*]————————————————————————————
[*] 2 2 9 13
[*]
[/list]

完整的查询如下(在oracle9.2.0.1中测试通过)：

with
age_list as
   (select rowNum age from all_all_tables where rownum<=36),
product_check as
(
    select
       age1.age youngest,
       age2.age middle,
       age3.age oldest,
       age1.age+age2.age+age3.age as sumed
    from age_list age1,age_list age2,age_list age3
    where age1.age<=age2.age and age2.age<=age3.age
    and age1.age*age2.age*age3.age=36
),
sumed_check as
(
   select youngest,middle,oldest,sumed
   from
      (
          select youngest,
                 middle,
                 oldest,
                 sumed,
                 count(*) over(partition by sumed) cnt
          from product_check               
      )
   where cnt>=2  
)
select youngest,middle,oldest
from sumed_check
where middle<oldest

----------------------------------------------------------------
oracle 中with的用法
当查询中多次用到某一部分时，可以用with语句创建一个公共临时表。因为子查询在内存临时表中，避免了重复解析，所以执行效率会提高不少。临时表在一次查询结束自动清除。
一般语法格式：

       with
alias_name1 as    (subquery1),
            alias_name2 as    (subQuery2),
            ……
            alias_nameN as    (subQueryN)
      select col1,col2…… col3
                 from alias_name1,alias_name2……,alias_nameN

例子：

      SQL> WITH
Q1 AS (SELECT 3 + 5 S FROM DUAL),
                Q2 AS (SELECT 3 * 5 M FROM DUAL),
                Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)
SELECT * FROM Q3;

[list]
[*]输出结果：
[*]S M S+M S*M
[*]---------- ---------- ---------- ----------
[*]8 15 23 120
[/list]

所有的子查询都可以用到，下面是转帖网上的一个UPDATE语句(url:http://www.oracle.com.cn/viewthread.php?tid=83530)：（

在讨论一个有关表的UPDATE时, 写了如下的SQL:<原始需求,请参考:http://www.oracle.com.cn/viewthr ... ghlight=&page=2>

SQL> update test2 set spc = (
   2       select substr(max(sys_connect_by_path(b.name, '-')),2) name
   3          from (select rn, skycode id,
   4                          decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + 1 sp,
   5                          decode(instr(skycode, '-', 1, rn), 0, length(skycode) + 1, instr(skycode, '-', 1, rn)) ep
   6                   from (select rownum rn from dual connect by rownum<=20) a, test2 b
   7                   where decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + instr(skycode, '-', 1, rn) > 0
   8                ) a, test b
   9          where b.citycode = substr(a.id, sp, ep-sp) and a.id=test2.skycode
10          start with rn=1 connect by rn-1=prior rn and a.id = prior a.id );

当时是在9.2.0.7下做的,没有问题,可以有朋友在9.2.0.1下,就会出现:

ORA-03113:通信通道文件结束
ORA-03114:未连接ORACLE

的错误提示, 这是9.2.0.1的一个BUG, 在多次的子查询时出现, 我试过,在9.2.0.5已经没有了,但不知道从那个版本ORACLE做了更正.

前段时间在写类似的多子查询的SELECT语句时, ORACLE9I提供的一个新子句: WITH在某种程度上解决了部分这类错误的出现. 经测试,原来同样的写法,也可以用于UPDATE中, 如上面的语句,可以用WITH改写为:

SQL> update test2 set spc = (
   2       with myque as (select rn, skycode id,
   3                          decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + 1 sp,
   4                          decode(instr(skycode, '-', 1, rn), 0, length(skycode) + 1,
   5                                                             instr(skycode, '-', 1, rn)) ep
   6                       from (select rownum rn from dual connect by rownum<=20) a, test2 b
   7                       where decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + instr(skycode, '-', 1, rn) > 0 )
   8       select substr(max(sys_connect_by_path(b.name, '-')),2) name
   9          from myque a, test b
10          where b.citycode = substr(a.id, sp, ep-sp) and a.id=test2.skycode
11          start with rn=1 connect by rn-1=prior rn and a.id = prior a.id );

已更新4行。