用一个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 语句汇总 Oracle 是一个功能强大且复杂的关系数据库管理系统,它提供了多种 SQL 语句来管理和操作数据库。在本文中,我们将详细介绍 Oracle 中常用的 SQL 语句,包括数据控制语句(DML)、数据定义...
### Oracle基本建表语句知识点总结 #### 一、创建用户 在Oracle数据库中,创建用户是基础操作之一。这通常用于控制不同开发者或应用程序之间的访问权限。 **语法:** ```sql CREATE USER <username> IDENTIFIED BY...
本文将详细介绍Oracle的基本建表语句及相关操作。 首先,创建用户是数据库管理的基础。Oracle中创建用户的基本语法如下: ```sql CREATE USER 用户名 IDENTIFIED BY 密码 DEFAULT TABLESPACE 表空间名 TEMPORARY ...
- `SELECT`语句基本兼容,但Oracle的`WITH`子句在DB2中可能需要额外的语法调整。 - Oracle的`ROWNUM`用于行号,DB2则使用`ROW_NUMBER()`函数。 - 分区查询,Oracle的`PARTITION BY`在DB2中对应`WINDOW`函数。 4....
### Oracle数据库中的START WITH 和 CONNECT BY 用法详解 在Oracle数据库中,处理层次结构数据时,`START WITH` 和 `CONNECT BY` 是非常有用的两个关键字。这些关键字可以帮助我们在查询时构建出树形或者层级结构的...
### Oracle万能查询语句详解 #### 一、概述 在Oracle数据库中,查询语句是数据检索的核心工具。本文将详细介绍一个复杂的Oracle查询语句,该语句涉及多个表的连接、序列、存储过程以及多表查询等相关知识点。通过...
本文将详细解析Oracle的基本建表语句以及相关的数据库操作。 首先,创建用户是数据库管理的第一步。以下是一个创建用户`han`的示例: ```sql CREATE USER han IDENTIFIED BY han DEFAULT TABLESPACE users ...
本篇将深入探讨Oracle关于日期的查询语句及其相关知识点。 1. 数据类型: Oracle提供了多种日期和时间数据类型,包括DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE以及TIMESTAMP WITH LOCAL TIME ZONE。其中,DATE...
### Oracle SQL语句分页问题详解 #### 一、引言 在数据库查询操作中,分页是一项常用且重要的功能,特别是在数据量较大的情况下。它能够有效地提高用户体验,并减轻服务器负担。本文将针对Oracle数据库中的SQL分页...
以下是对标题"Oracle数据库语句大全"中涉及的一些核心知识点的详细说明: 一、数据库管理 1. **创建表空间**:Oracle数据库允许创建表空间以组织数据文件。`CREATE TABLESPACE`语句用于创建一个新的表空间,例如: ...
Oracle 基本语句 Oracle 是一种广泛使用的关系数据库管理系统,它提供了丰富的语句来操作和管理数据库。下面是 Oracle 的一些基本语句。 ### 创建用户 在 Oracle 中,创建用户需要使用 CREATE USER 语句,并且...
根据提供的文件信息,我们可以深入探讨Oracle数据库中的几个关键知识点:序列的创建、各种类型的约束以及用户的解锁操作。接下来,我们将详细地对这些知识点进行解析。 ### 一、Oracle中序列的创建 序列在Oracle...
当查询中多次用到某一部分时,可以用Oracle with语句创建一个公共临时表。因为子查询在内存临时表中,避免了重复解析,所以执行效率会提高不少。临时表在一次查询结束自动清除。 一般语法格式: 代码如下:with ...
9. **分页**:`LIMIT`和`OFFSET`在某些数据库系统中用于实现数据分页,但Oracle使用`ROWNUM`或`FETCH NEXT`结合`WITH TIES`来实现类似功能。 10. **动态SQL**:允许在运行时构建和执行SQL语句,增强了SQL的灵活性,...
### Oracle常见语句详解 #### 一、连接与断开数据库 在操作Oracle数据库时,首先需要通过SQL*Plus工具连接到数据库。连接命令的基本格式为`sqlplus 用户名/密码@实例名`,例如`sqlplus scott/orcl@orcl`用于以scott...
我们都知道在9i之前,要想获得建表和索引的语句是一件很麻烦的...在 Oracle 9i中,我们可以直接通过执行dbms_metadata从数据字典中查处DDL语句。使用这个功能强大的工具,我们可以获得单个对象或整个SCHEMA 的DDL语句。
* 用 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全称为Procedural Language/Structured Query Language,它是SQL的扩展,增加了过程化编程元素。它允许开发人员编写复杂的业务逻辑,...
### Oracle操作语句集锦详解 #### 一、Oracle 命令行工具与基本语法 **1. 启动 SQLPlus:** ```bash sqlplus /nolog ``` 此命令用于启动 SQLPlus 工具,不进行任何登录操作。 **2. 连接到数据库:** ```bash conn...