sql - 如何在oracle中的多选中避免相同的子查询？

Question

这是使用相同子查询的三个不同选择。如何使用子查询结果而不是再次执行子查询。

SELECT  *
FROM    Address
WHERE   address_key IN
        (
        SELECT  address_key
        FROM    person_address
        WHERE   peson_key IN (person_list)
        );   -- person_list := '1,2,3,4'

SELECT  *
FROM    Phone 
WHERE   phone_key IN
        (
        SELECT  address_key
        FROM    person_address
        WHERE   peson_key IN (person_list)
        );

SELECT  *
FROM    Email
WHERE   address_key IN
        (
        SELECT  address_key
        FROM    person_address
        WHERE   peson_key IN (person_list)
        );

Answer 1

使用 with 子句。我没有重新创建您的确切示例问题，但是可以将任意数量的重复子查询放入 WITH 子句中，然后在查询中引用。

WITH  address_keys as (
        SELECT  address_key
        FROM    person_address
        WHERE   peson_key IN (person_list)
        )
Select * from table1, table2, address_keys
where table1.address_key = address_keys.address_key
and table2.address_key = address_keys.address_key