`

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
     }

    得出的结果是
  •    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   

   现在 年龄之和等于一个数,知道了现场人数仍不能确定,——说明查询集合的和至少有两行的值是一样的。
   缩小输出范围
    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
   }

   输出结果:
  •     Y              M              O              S
  • ————————————————————————————
  •     1              6              6              13
  •     2              2              9              13

    接着“年龄大的人的宠物仓鼠有个腿是木质的”,表明年龄大的人的年龄比中间那个要大。
    所以
    select yongest,middle,oldest from sumed_checked
where oldest>middle

    得出结果:
  •     Y              M              O              S
  • ————————————————————————————
  •     2              2              9              13
  •  


完整的查询如下(在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;

  • 输出结果:
  • S M S+M S*M
  • ---------- ---------- ---------- ----------
  • 8 15 23 120


     所有的子查询都可以用到,下面是转帖网上的一个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行。
分享到:
评论

相关推荐

    oracle常用SQL语句(汇总版).docx

    Oracle 常用 SQL 语句汇总 Oracle 是一个功能强大且复杂的关系数据库管理系统,它提供了多种 SQL 语句来管理和操作数据库。在本文中,我们将详细介绍 Oracle 中常用的 SQL 语句,包括数据控制语句(DML)、数据定义...

    Oracle基本建表语句

    ### Oracle基本建表语句知识点总结 #### 一、创建用户 在Oracle数据库中,创建用户是基础操作之一。这通常用于控制不同开发者或应用程序之间的访问权限。 **语法:** ```sql CREATE USER &lt;username&gt; IDENTIFIED BY...

    Oracle_基本建表语句

    本文将详细介绍Oracle的基本建表语句及相关操作。 首先,创建用户是数据库管理的基础。Oracle中创建用户的基本语法如下: ```sql CREATE USER 用户名 IDENTIFIED BY 密码 DEFAULT TABLESPACE 表空间名 TEMPORARY ...

    DB2与ORACLE常用语句对照

    - `SELECT`语句基本兼容,但Oracle的`WITH`子句在DB2中可能需要额外的语法调整。 - Oracle的`ROWNUM`用于行号,DB2则使用`ROW_NUMBER()`函数。 - 分区查询,Oracle的`PARTITION BY`在DB2中对应`WINDOW`函数。 4....

    oracle数据库startwith用法

    ### Oracle数据库中的START WITH 和 CONNECT BY 用法详解 在Oracle数据库中,处理层次结构数据时,`START WITH` 和 `CONNECT BY` 是非常有用的两个关键字。这些关键字可以帮助我们在查询时构建出树形或者层级结构的...

    Oracle万能查询语句

    ### Oracle万能查询语句详解 #### 一、概述 在Oracle数据库中,查询语句是数据检索的核心工具。本文将详细介绍一个复杂的Oracle查询语句,该语句涉及多个表的连接、序列、存储过程以及多表查询等相关知识点。通过...

    Oracle_基本建表语句.doc

    本文将详细解析Oracle的基本建表语句以及相关的数据库操作。 首先,创建用户是数据库管理的第一步。以下是一个创建用户`han`的示例: ```sql CREATE USER han IDENTIFIED BY han DEFAULT TABLESPACE users ...

    oracle关于日期的查询语句

    本篇将深入探讨Oracle关于日期的查询语句及其相关知识点。 1. 数据类型: Oracle提供了多种日期和时间数据类型,包括DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE以及TIMESTAMP WITH LOCAL TIME ZONE。其中,DATE...

    Oracle SQL语句分页问题

    ### Oracle SQL语句分页问题详解 #### 一、引言 在数据库查询操作中,分页是一项常用且重要的功能,特别是在数据量较大的情况下。它能够有效地提高用户体验,并减轻服务器负担。本文将针对Oracle数据库中的SQL分页...

    Oracle数据库语句大全.docx

    以下是对标题"Oracle数据库语句大全"中涉及的一些核心知识点的详细说明: 一、数据库管理 1. **创建表空间**:Oracle数据库允许创建表空间以组织数据文件。`CREATE TABLESPACE`语句用于创建一个新的表空间,例如: ...

    Oracle基本语句

    Oracle 基本语句 Oracle 是一种广泛使用的关系数据库管理系统,它提供了丰富的语句来操作和管理数据库。下面是 Oracle 的一些基本语句。 ### 创建用户 在 Oracle 中,创建用户需要使用 CREATE USER 语句,并且...

    oracle中sql语句用法

    根据提供的文件信息,我们可以深入探讨Oracle数据库中的几个关键知识点:序列的创建、各种类型的约束以及用户的解锁操作。接下来,我们将详细地对这些知识点进行解析。 ### 一、Oracle中序列的创建 序列在Oracle...

    oracle while的用法示例分享

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

    Oracle的sql语句练习题及参考答案

    9. **分页**:`LIMIT`和`OFFSET`在某些数据库系统中用于实现数据分页,但Oracle使用`ROWNUM`或`FETCH NEXT`结合`WITH TIES`来实现类似功能。 10. **动态SQL**:允许在运行时构建和执行SQL语句,增强了SQL的灵活性,...

    oracle 常见语句简析

    ### Oracle常见语句详解 #### 一、连接与断开数据库 在操作Oracle数据库时,首先需要通过SQL*Plus工具连接到数据库。连接命令的基本格式为`sqlplus 用户名/密码@实例名`,例如`sqlplus scott/orcl@orcl`用于以scott...

    Oracle中轻松取得建表和索引的DDL语句

    我们都知道在9i之前,要想获得建表和索引的语句是一件很麻烦的...在 Oracle 9i中,我们可以直接通过执行dbms_metadata从数据字典中查处DDL语句。使用这个功能强大的工具,我们可以获得单个对象或整个SCHEMA 的DDL语句。

    数据库 创建索引 sql oracle

    * 用 Transact-SQL 语句创建索引:使用 CREATE INDEX 语句创建索引,例如 CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ...n ] ) [ WITH [PAD_...

    pl/sql及常见实用oracle语句学习

    在本文中,我们将深入探讨PL/SQL以及Oracle数据库中的一些常见实用语句。 PL/SQL全称为Procedural Language/Structured Query Language,它是SQL的扩展,增加了过程化编程元素。它允许开发人员编写复杂的业务逻辑,...

    Oracle操作语句集锦

    ### Oracle操作语句集锦详解 #### 一、Oracle 命令行工具与基本语法 **1. 启动 SQLPlus:** ```bash sqlplus /nolog ``` 此命令用于启动 SQLPlus 工具,不进行任何登录操作。 **2. 连接到数据库:** ```bash conn...

Global site tag (gtag.js) - Google Analytics