查看: 722|回复: 9

[SQL] 11g如何实现outer apply语法

[复制链接]
论坛徽章:
17
优秀写手
日期:2014-02-27 06:00:13秀才
日期:2017-08-11 15:37:32弗兰奇
日期:2017-07-04 09:16:01秀才
日期:2017-06-29 10:16:48乌索普
日期:2017-05-26 08:58:24娜美
日期:2017-05-18 16:07:23ITPUB15周年纪念
日期:2017-05-02 15:22:36妮可·罗宾
日期:2017-04-06 10:06:19处女座
日期:2016-03-10 09:03:26白羊座
日期:2015-10-10 15:01:39
发表于 2020-7-29 10:41 | 显示全部楼层 |阅读模式
需求:
有A、B两个表,关联B表的时候是根据A表的条件进行聚合或其他处理后再关联。例如以下12c outer apply 的语法。想问下在11g如何实现?
尝试过:
子查询,如果需要B表的多个字段就很麻烦,无法避免多次表扫描;
B表先进行exists过滤, 如果A表是多个表关联的结果,会然后sql变得臃肿复杂;
管道函数,没有测试过效率。
  1. with tmp as
  2. (select level as l from dual connect by level <=20),
  3. tmp1 as
  4. (select level as l, dbms_random.value as v from dual connect by level <=20)
  5. select * from tmp t
  6. outer apply (select sum(v) as v1 ,count(l) as v2 from tmp1 t1
  7. where t1.l<=t.l)t2
复制代码


论坛徽章:
536
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2020-7-29 15:58 | 显示全部楼层
与上面 outer apply 等价的传统写法

with tmp as
(select level as l from dual connect by level <=20),
tmp1 as
(select level as l, dbms_random.value as v from dual connect by level <=20)
select t.l,sum(s.v),count(*) v2
from tmp t left outer join tmp1 s
   on s.l <= t.l
group by t.l
order by t.l

使用道具 举报

回复
论坛徽章:
17
优秀写手
日期:2014-02-27 06:00:13秀才
日期:2017-08-11 15:37:32弗兰奇
日期:2017-07-04 09:16:01秀才
日期:2017-06-29 10:16:48乌索普
日期:2017-05-26 08:58:24娜美
日期:2017-05-18 16:07:23ITPUB15周年纪念
日期:2017-05-02 15:22:36妮可·罗宾
日期:2017-04-06 10:06:19处女座
日期:2016-03-10 09:03:26白羊座
日期:2015-10-10 15:01:39
 楼主| 发表于 2020-7-29 17:47 | 显示全部楼层
solomon_007 发表于 2020-7-29 15:58
与上面 outer apply 等价的传统写法with tmp as(select level as l from dual connect by level

emmm, 等价改写是一种办法。但是有些情景就不太适用,或者说改写会原先的sql变得臃肿。我刚刚测试管道函数的效率还可以,对原来的sql结构也不需要做很大改变。比如:
情景1,如果需要查询A表的十多个字段,并且这些字段可能需要进行一些函数处理,那就必须先GROUP BY 这些字段,聚合B表。然后外层在做处理。
情景2,比如需要对B表进行筛选:
  1. with tmp as
  2. (select level as l from dual connect by level <=5),
  3. tmp1 as
  4. (select mod(level,5)+1 as l, level as rn , chr(64+level) as s from dual connect by level <=3)
  5. select * from tmp t
  6. outer apply (select * from (select s,row_number()over(partition by l order by rn) as rn from tmp1 t1
  7. where t1.l=t.l) where rn=1)t2
复制代码


使用道具 举报

回复
论坛徽章:
527
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2020-7-29 21:17 | 显示全部楼层
outer apply的本质是相关子查询,即把外层表的值传入子查询中做驱动条件或者参与计算。这和标量子查询很像,但是标量子查询有如下限制:
不能返回多于一行;
外面的值只能向里面传一层,再深入就不行。
楼主的例子恰好满足,所以是可以改用标量子查询的:

with tmp as
(select level as l from dual connect by level <=20),
tmp1 as
(select level as l, dbms_random.value as v from dual connect by level <=20)
select t.*
      ,(select sum(v) ||','||count(l) as result
          from tmp1 t1
        where t1.l<=t.l
       )
  from tmp t;

上面的结果包含了两个列,可以在外面嵌套一层解析出来。

如果子查询返回多于一行,比如把上面的聚合函数拿掉:

with tmp as
(select level as l from dual connect by level <=20),
tmp1 as
(select level as l, dbms_random.value as v from dual connect by level <=20)
select * from tmp t
outer apply (select v as v1 ,l as v2 from tmp1 t1
where t1.l<=t.l)t2

此时标量子查询就无法胜任。一种办法是利用ORACLE的嵌套表属性,它允许将一个嵌套表类型的列用TABLE()展开成关系表。这里的tmp并没有嵌套表类型的列,可以用子查询造出一个来,而且也没有传递层数的限制:

create or replace type ty_nt_row as object (v1 number,v2 number)
/

create or replace type ty_nt as table of ty_nt_row
/


with tmp as
(select level as l from dual connect by level <=20),
tmp1 as
(select level as l, dbms_random.value as v from dual connect by level <=20)
select *
  from tmp t
      ,table(cast(multiset(-----multiset 里面就是outer apply的查询逻辑,只是结果要变成对象类型ty_nt_row
                           select ty_nt_row(v,l)
                             from tmp1 t1
                            where t1.l<=t.l
                          )  ---- end of multiset
             as ty_nt) --- end of cast
            )
;

使用道具 举报

回复
论坛徽章:
403
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2020-7-30 06:48 来自手机 | 显示全部楼层
好,记录下

使用道具 举报

回复
论坛徽章:
17
优秀写手
日期:2014-02-27 06:00:13秀才
日期:2017-08-11 15:37:32弗兰奇
日期:2017-07-04 09:16:01秀才
日期:2017-06-29 10:16:48乌索普
日期:2017-05-26 08:58:24娜美
日期:2017-05-18 16:07:23ITPUB15周年纪念
日期:2017-05-02 15:22:36妮可·罗宾
日期:2017-04-06 10:06:19处女座
日期:2016-03-10 09:03:26白羊座
日期:2015-10-10 15:01:39
 楼主| 发表于 2020-7-30 13:32 | 显示全部楼层
emmm, 等价改写是一种办法。但是有些情景就不太适用,或者说改写会原先的sql变得臃肿。我刚刚测试管道函数的效率还可以,对原来的sql结构也不需要做很大改变。比如: 情景1,如果需要查询A表的十多个字段,并且这些字段可能需要进行一些函数处理,那就必须先GROUP BY 这些字段,聚合B表。然后外层在做处理。 情景2,比如需要对B表进行筛选:
  1. with tmp as (select level as l from dual connect by level <=5), tmp1 as (select mod(level,5)+1 as l, level as rn , chr(64+level) as s from dual connect by level <=3) select * from tmp t outer apply (select * from (select s,row_number()over(partition by l order by rn) as rn from tmp1 t1 where t1.l=t.l) where rn=1)t2
复制代码

使用道具 举报

回复
论坛徽章:
17
优秀写手
日期:2014-02-27 06:00:13秀才
日期:2017-08-11 15:37:32弗兰奇
日期:2017-07-04 09:16:01秀才
日期:2017-06-29 10:16:48乌索普
日期:2017-05-26 08:58:24娜美
日期:2017-05-18 16:07:23ITPUB15周年纪念
日期:2017-05-02 15:22:36妮可·罗宾
日期:2017-04-06 10:06:19处女座
日期:2016-03-10 09:03:26白羊座
日期:2015-10-10 15:01:39
 楼主| 发表于 2020-7-31 09:33 | 显示全部楼层
人气这么少了,为什么还要审核啊。回复的一直在审核

使用道具 举报

回复
论坛徽章:
527
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2020-8-1 01:47 | 显示全部楼层
管道函数的原理和我的写法是一样的,也要用到TABLE()操作符,也要定义嵌套表。
你的情景2用我的写法是这样:
create or replace type ty_nt_row as object (s varchar2(10),rn number)
/

create or replace type ty_nt as table of ty_nt_row
/

with tmp as (select level as l from dual connect by level <=5)
, tmp1 as (select mod(level,5)+1 as l, level as rn , chr(64+level) as s from dual connect by level <=3)
select *
  from tmp t
      ,table(cast(multiset(-----multiset 里面就是outer apply的查询逻辑,只是结果要变成对象类型ty_nt_row
                           select ty_nt_row(s,rn)
                             from (select s,row_number()over(partition by l order by rn) as rn
                                    from tmp1 t1
                                   where t1.l=t.l)
                            where rn=1
                          )  ---- end of multiset
             as ty_nt) --- end of cast
            )(+)
;

使用道具 举报

回复
论坛徽章:
17
优秀写手
日期:2014-02-27 06:00:13秀才
日期:2017-08-11 15:37:32弗兰奇
日期:2017-07-04 09:16:01秀才
日期:2017-06-29 10:16:48乌索普
日期:2017-05-26 08:58:24娜美
日期:2017-05-18 16:07:23ITPUB15周年纪念
日期:2017-05-02 15:22:36妮可·罗宾
日期:2017-04-06 10:06:19处女座
日期:2016-03-10 09:03:26白羊座
日期:2015-10-10 15:01:39
 楼主| 发表于 2020-8-1 21:07 | 显示全部楼层
newkid 发表于 2020-8-1 01:47
管道函数的原理和我的写法是一样的,也要用到TABLE()操作符,也要定义嵌套表。你的情景2用我的写法是这样: ...

table 貌似不能嵌套多层, 这个SQL 提示T.L标识符无效了

使用道具 举报

回复
论坛徽章:
527
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2020-8-3 21:34 | 显示全部楼层
chengccy2010 发表于 2020-8-1 21:07
table 貌似不能嵌套多层, 这个SQL 提示T.L标识符无效了

我记得以前在某个版本可以用,应该是我记错了,这个方法在11G确实和标量子查询一样受限制,那还是用PIPELINE函数吧。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表