主页 / dba / 问题 / 122841
Accepted
David Balažic
Asked: 2015-12-04 13:16:09 +0800 CST

如何重构这个子查询只写一次？

  • 772
procedure foo(p1 IN INTEGER,
    p2 IN INTEGER,
    ooo out sys_refcursor)
is
begin

open ooo for
select T1.*
from T1
where T1.x=p1
    and (   
        p2 in (SELECT z from T2 where T2.someId=T1.SomeId and T2.y=12)
    or
        not exists (SELECT z from T2 where T2.someId=T1.SomeId and T2.y=12)
        );
end;

子查询两次完全相同。有没有办法将其重构为仅指定一次？至少在 Oracle 上。

一种方法是将其用作连接：

procedure foo(p1 IN INTEGER,
    p2 IN INTEGER,
    ooo out sys_refcursor)
is
begin

open ooo for
select unique T1.*
from T1
left join T2 on T2.someId=T1.SomeId and T2.y=12
where T1.x=p1
    and (   
        p2 = T2.z
    or
        T2.z is null -- T2.z is a NOT NULL column
        );
end;

但这并不等同（我添加了 UNIQUE 以过滤掉因连接而相乘的行，但这也从 T1 中删除了可能正确的重复行）。

PS：用上面的两个查询和下面的两个答案制作了一个SQL Fiddle 。

oracle

1 个回答

  1. Best Answer

    我想出了这个：

    procedure foo(p1 IN INTEGER,
    p2 IN INTEGER,
    ooo out sys_refcursor)
is
begin

open ooo for
with sq as (SELECT z, someId from T2 where T2.y=12)
select T1.*
from T1
where T1.x=p1
    and (   
        (p2,T1.someId) in (select * from sq)
    or
        not exists (select * from sq where sq.someId=T1.someId)
        );
end;
    • 1

相关问题