★ oracle子查询分解(分而治之)
来源:互联网 发布:放大镜软件下载 编辑:程序博客网 时间:2024/05/14 14:08
oracle子查询分解(分而治之)
url:http://hi.baidu.com/danghj/blog/item/fa2a7363971bff670c33fa0e.html
2009-01-07 15:23
用一个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在支持子查询的地方,同时也支持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在支持子查询的地方,同时也支持WITH的操作, 本人认为,这样一来,可以让开发人员有更多的机会,写出高效的单个SQL语句. 特别是在多个子查询中多次对同一基表进行访问时.
这是本人的更解, 不知对否, 请各位大虾批评指正.
- ★ oracle子查询分解(分而治之)
- 最大子段和(分而治之求解)
- 最大子序列的分而治之 o(nlogn)
- 原创:oracle 子查询介绍 (七)
- Oracle学习(6):子查询
- Oracle数据库(六)子查询
- ORACLE中的子查询
- ORACLE中的子查询
- ORACLE中的子查询
- oracle子查询用途
- oracle联合子查询
- Oracle----子查询
- Oracle 子查询
- Oracle(六)----------------子查询
- oracle子查询详解
- oracle子查询
- oracle 子查询介绍
- Oracle子查询
- VB6 中将数据导出到 Excel 提速之法
- 领先的易物网站-舍得网基于J2ee Resin实现的技术内幕(二)
- 使用DOS命令net share共享,cacls分配权限
- c# 中的数组的操作
- 35岁前必学的理财窍门
- ★ oracle子查询分解(分而治之)
- JAVA开源选择方案
- 遇到一个怪问题,在网上找到了答案,嘿嘿
- Oracle的优化器
- e家数据挖掘_项目进度时间doc
- EXCEL学习-INDEX函数
- [连载]基于消息驱动的面向对象通用C/S应用框架(一)
- Linux 2.6 内核Epoll用法举例说明
- 关于Java栈与堆的思考