具有多个列的Oracle SQL查询使用带OR的同一子查询

Question

需要帮助简化以下带有OR条件的Oracle SQL查询：是否可以使用一个单独的选择查询而不是多个。

对于这个子查询"从usr中选择usr_key，其中usr_login='abcd'"，我能够将它变成单个，而不是写4次，因为它只给出一个值。但对于第二个子查询，它正在写入多个值，因此我无法组合它们。

select * from catalog  where 
(
(select usr_key from usr where usr_login='abcd') in (approver_user,CERTIFIER_USER,FULFILLMENT_USER) 
or ('abcd') in (EMERGENCYAPPROVERUSER,SPECIALAPPROVERUSER)
or approver_role IN 
(select ugp.ugp_key from ugp , usg,usr uuu where ugp.ugp_key=usg.ugp_key and usg.usr_key=uuu.usr_key  and uuu.usr_login='abcd')
or CERTIFIER_ROLE IN 
(select ugp.ugp_key from ugp , usg,usr uuu where ugp.ugp_key=usg.ugp_key and usg.usr_key=uuu.usr_key  and uuu.usr_login='abcd')
or FULFILLMENT_ROLE IN 
(select ugp.ugp_key from ugp , usg,usr uuu where ugp.ugp_key=usg.ugp_key and usg.usr_key=uuu.usr_key  and uuu.usr_login='abcd')
)
and entity_type='Entitlement';

Answer 1

使用WITH子句

WITH some_query AS(
select ugp.ugp_key from ugp , usg,usr uuu 
where ugp.ugp_key=usg.ugp_key and usg.usr_key=uuu.usr_key  and uuu.usr_login='abcd'
)
select * from catalog  
where 
(
(select usr_key from usr where usr_login='abcd') in (approver_user,CERTIFIER_USER,FULFILLMENT_USER) 
or 
('abcd') in (EMERGENCYAPPROVERUSER,SPECIALAPPROVERUSER)
or 
approver_role IN    (SELECT * FROM some_query)
or 
CERTIFIER_ROLE IN   (SELECT * FROM some_query)
or 
FULFILLMENT_ROLE IN (SELECT * FROM some_query)
)
and entity_type='Entitlement';