ORACLE10没有OUTER APPLY，请问用什么替代方法

servercg 2018-11-07 04:08:28

...全文

501 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

nayi_224 2018-11-08

打赏
举报

不需要join，单表查询就够了

with tab1 as (
select 'A' id, to_date('2018-04-01', 'yyyy-mm-dd') in_time, 3 cnt from dual union all
select 'A' id, to_date('2018-09-01', 'yyyy-mm-dd') in_time, 3 cnt from dual union all
select 'A' id, to_date('2018-10-03', 'yyyy-mm-dd'), 5 from dual union all
select 'A' id, to_date('2018-11-04', 'yyyy-mm-dd'), 5 from dual union all
select 'A' id, to_date('2018-11-02', 'yyyy-mm-dd'), 1 from dual
)
,tab2 as (
select t1.*,
       sum(t1.cnt) over(order by t1.in_time desc) sum_cnt
  from tab1 t1
)
,tab3 as (
select t1.*,
       case when 7 >= sum_cnt
         then cnt
            when 7 >= lead(sum_cnt) over(order by t1.in_time)
         then 7 - lead(sum_cnt) over(order by t1.in_time)
       else 0
       end result_
  from tab2 t1
)
,tab4 as (
select sysdate - level * 30 st, sysdate - (level - 1) * 30 ed, level lv from dual connect by level <= 4
)
select sum(decode(t2.lv, 1, result_, 0)) "30天内",
       sum(decode(t2.lv, 2, result_, 0)) "60天内",
       sum(decode(t2.lv, 3, result_, 0)) "90天内",
       sum(decode(t2.lv, 4, result_, 0)) "90以上"
  from tab3 t1, tab4 t2
 where (t1.in_time between t2.st and t2.ed and t2.lv != 4)
    or (t1.in_time < t2.ed and t2.lv = 4)
;

servercg 2018-11-08

打赏
举报

--入库单主表

Create Global Temporary TABLE t1 (rid int,code char(10),intime date)

 INSERT INTO t1 VALUES (1,'A', to_date(1,'rk001','2018-09-01 18:31:34','YYYY-MM-DD HH24:MI:SS' ))

 INSERT INTO t1 VALUES (2,'A', to_date(2,'rk002','2018-10-03 09:31:34','YYYY-MM-DD HH24:MI:SS' ))

 INSERT INTO t1 VALUES (3,'A', to_date(1,'rk003','2018-11-02 18:31:34','YYYY-MM-DD HH24:MI:SS' ))

--入库单子表，子表入库单号对应到主表

Create Global Temporary TABLE t2 (rid INT, in_code char(10), productName CHAR(10),inCnt INT)

  INSERT INTO t2 VALUES (1,'rk001','A',3)

  INSERT INTO t2 VALUES (2,'rk002','A',5)

  INSERT INTO t2 VALUES (3,'rk003','A',6)

 

--计算后的当前结存表，假如当前库存数为7

Create Global Temporary t3 TABLE (productName NVARCHAR(10),jcCnt INT)

INSERT INTO @t2 VALUES ('A',7)

需要得到以下结果

卖水果的net 2018-11-08

打赏
举报

给出你的数据用 insert 形式的。预期结果用 excel 画一下

servercg 2018-11-08

打赏
举报

完美结贴，谢谢

nayi_224 2018-11-08

打赏
举报

引用 7 楼 servercg 的回复:

[quote=引用 6 楼 nayi_224 的回复:] 不需要join，单表查询就够了

with tab1 as (
select 'A' id, to_date('2018-04-01', 'yyyy-mm-dd') in_time, 3 cnt from dual union all
select 'A' id, to_date('2018-09-01', 'yyyy-mm-dd') in_time, 3 cnt from dual union all
select 'A' id, to_date('2018-10-03', 'yyyy-mm-dd'), 5 from dual union all
select 'A' id, to_date('2018-11-04', 'yyyy-mm-dd'), 5 from dual union all
select 'A' id, to_date('2018-11-02', 'yyyy-mm-dd'), 1 from dual
)
,tab2 as (
select t1.*,
       sum(t1.cnt) over(order by t1.in_time desc) sum_cnt
  from tab1 t1
)
,tab3 as (
select t1.*,
       case when 7 >= sum_cnt
         then cnt
            when 7 >= lead(sum_cnt) over(order by t1.in_time)
         then 7 - lead(sum_cnt) over(order by t1.in_time)
       else 0
       end result_
  from tab2 t1
)
,tab4 as (
select sysdate - level * 30 st, sysdate - (level - 1) * 30 ed, level lv from dual connect by level <= 4
)
select sum(decode(t2.lv, 1, result_, 0)) "30天内",
       sum(decode(t2.lv, 2, result_, 0)) "60天内",
       sum(decode(t2.lv, 3, result_, 0)) "90天内",
       sum(decode(t2.lv, 4, result_, 0)) "90以上"
  from tab3 t1, tab4 t2
 where (t1.in_time between t2.st and t2.ed and t2.lv != 4)
    or (t1.in_time < t2.ed and t2.lv = 4)
;

感谢nayi_224，这就是我要的，美中不足的是没有按品名分组，我借用了谢谢[/quote] 分组就这样写

with tab1 as (
select 'A' id, to_date('2018-04-01', 'yyyy-mm-dd') in_time, 3 cnt from dual union all
select 'A' id, to_date('2018-09-01', 'yyyy-mm-dd') in_time, 3 cnt from dual union all
select 'B' id, to_date('2018-09-01', 'yyyy-mm-dd') in_time, 3 cnt from dual union all
select 'A' id, to_date('2018-10-03', 'yyyy-mm-dd'), 5 from dual union all
select 'A' id, to_date('2018-11-04', 'yyyy-mm-dd'), 5 from dual union all
select 'B' id, to_date('2018-11-04', 'yyyy-mm-dd'), 5 from dual union all
select 'A' id, to_date('2018-11-02', 'yyyy-mm-dd'), 1 from dual
)
,tab2 as (
select t1.*,
       sum(t1.cnt) over(partition by t1.id order by t1.in_time desc) sum_cnt
  from tab1 t1
)
,tab3 as (
select t1.*,
       case when 7 >= sum_cnt
         then cnt
            when 7 >= lead(sum_cnt) over(partition by t1.id order by t1.in_time)
         then 7 - lead(sum_cnt) over(partition by t1.id order by t1.in_time)
       else 0
       end result_
  from tab2 t1
)
,tab4 as (
select sysdate - level * 30 st, sysdate - (level - 1) * 30 ed, level lv from dual connect by level <= 4
)
select t1.id,
       sum(decode(t2.lv, 1, result_, 0)) "30天内",
       sum(decode(t2.lv, 2, result_, 0)) "60天内",
       sum(decode(t2.lv, 3, result_, 0)) "90天内",
       sum(decode(t2.lv, 4, result_, 0)) "90以上"
  from tab3 t1, tab4 t2
 where (t1.in_time between t2.st and t2.ed and t2.lv != 4)
    or (t1.in_time < t2.ed and t2.lv = 4)
 group by t1.id
;

servercg 2018-11-08

打赏
举报

引用 6 楼 nayi_224 的回复:

不需要join，单表查询就够了

with tab1 as (

select 'A' id, to_date('2018-04-01', 'yyyy-mm-dd') in_time, 3 cnt from dual union all

select 'A' id, to_date('2018-09-01', 'yyyy-mm-dd') in_time, 3 cnt from dual union all

select 'A' id, to_date('2018-10-03', 'yyyy-mm-dd'), 5 from dual union all

select 'A' id, to_date('2018-11-04', 'yyyy-mm-dd'), 5 from dual union all

select 'A' id, to_date('2018-11-02', 'yyyy-mm-dd'), 1 from dual

)

,tab2 as (

select t1.*,

       sum(t1.cnt) over(order by t1.in_time desc) sum_cnt

  from tab1 t1

)

,tab3 as (

select t1.*,

       case when 7 >= sum_cnt

         then cnt

            when 7 >= lead(sum_cnt) over(order by t1.in_time)

         then 7 - lead(sum_cnt) over(order by t1.in_time)

       else 0

       end result_

  from tab2 t1

)

,tab4 as (

select sysdate - level * 30 st, sysdate - (level - 1) * 30 ed, level lv from dual connect by level <= 4

)

select sum(decode(t2.lv, 1, result_, 0)) "30天内",

       sum(decode(t2.lv, 2, result_, 0)) "60天内",

       sum(decode(t2.lv, 3, result_, 0)) "90天内",

       sum(decode(t2.lv, 4, result_, 0)) "90以上"

  from tab3 t1, tab4 t2

 where (t1.in_time between t2.st and t2.ed and t2.lv != 4)

    or (t1.in_time < t2.ed and t2.lv = 4)

;

感谢nayi_224，这就是我要的，美中不足的是没有按品名分组，我借用了谢谢

servercg 2018-11-07

打赏
举报

我在做的需求是库龄分析报表，计算出了时间段内入库量，和当前结存，需要用先入先出原则得出剩余量

品名时间数量
A 2018-9-1 3
A 2018-10-3 5
A 2018-11-2 6

加入当前库存7
结果应该是

30天内 60天内 90天内
6 1 0

servercg 2018-11-07

打赏
举报

left join 和outer apply 有区别，我需要用outer apply做子查询，比如下面一段

select * FROM CTE_1 A
OUTER APPLY (SELECT SUM([入库数量]) AS SUB_TOTAL FROM CTE_1 WHERE 产品=A.产品 AND 完整时间>=A.完整时间) AS B

用left join的话，A.产品就会报错找不到

卖水果的net 2018-11-07