鑒于此 SQL：
select * from table1
where
table1.columnFoo = 123
and
(
some_value is null
or
some_value in (select column1 from table2 where table1.colX=table2.colY)
or
not exists (select column1 from table2 where table1.colX=table2.colY)
);
-- some_value is a constant or an input parameter in an (PL/)SQL procedure
-- if it is non null, then we want to filter by it. Except if the list selection is empty.
有沒有辦法將“串列中或串列中為空”部分寫得更短？最好以只包含一次串列的方式（參見Don't_repeat_yourself原則）
我對 Oracle SQL 或 PL/SQL 感興趣，但也歡迎提供其他資訊。
根據要求，在 SQL*Plus 中運行的 MRE：
create table table1 as select 1 id, 'one' name , 12 price from dual
union select 2 , 'two' , 22 from dual
union select 3 , 'thr' , 33 from dual;
create table table2 as select 1 id1, 88 idX, sysdate-1 validDate from dual -- valid
union select 1 , 99 , sysdate 2 from dual -- these two are not valid (yet)
union select 2 , 99 , sysdate 3 from dual;
var some_value number
--exec :some_value := 3 -- uncomment for non null values
with cte as (select id1,idX from table2 where validDate<sysdate)
select * from table1
where
table1.price > 10
and
(
:some_value is null
or
:some_value in (select idX from cte where table1.id=cte.id1)
or
not exists (select idX from cte where table1.id=cte.id1)
);
uj5u.com熱心網友回復：
從 Oracle 12 開始，您可以使用LATERAL帶有條件聚合的連接：
SELECT t1.*
FROM table1 t1
CROSS JOIN LATERAL(
SELECT 1 AS matched
FROM table2 t2
WHERE t1.colX=t2.colY
HAVING COUNT(*) = 0
OR COUNT(CASE t2.column1 WHEN :some_value THEN 1 END) > 0
) t2
WHERE t1.columnFoo = 123
AND ( :some_value is null OR t2.matched = 1);
或類似的技術使用EXISTS：
select *
from table1
WHERE columnFoo = 123
AND ( :some_value is null
OR EXISTS(
SELECT 1
FROM table2
WHERE table1.colX=colY
HAVING COUNT(*) = 0
OR COUNT(CASE column1 WHEN :some_value THEN 1 END) > 0
)
);
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/361924.html