sql - Oracle SQL : Re-use subquery for CASE WHEN without having to repeat subquery

Question

我有一个 Oracle SQL 查询，它在其列输出中包含计算。在这个简化的示例中，我们正在寻找日期在某个范围内的记录，其中某些字段与特定事物相匹配；然后对于这些记录，取 ID(不是唯一的)并再次搜索表以查找具有相同 ID 的记录，但某些字段与其他字段匹配并且日期在主记录的日期之前。然后返回最早的此类日期。以下代码完全按预期工作:

SELECT
    TblA.ID, /* Not a primary key: there may be more than one record with the same ID */
    (
    SELECT
        MIN(TblAAlias.SomeFieldDate)
    FROM
        TableA TblAAlias
    WHERE
        TblAAlias.ID = TblA.ID /* Here is the link reference to the main query */
        TblAAlias.SomeField = 'Another Thing'
        AND TblAAlias.SomeFieldDate <= TblA.SomeFieldDate /* Another link reference */
    ) AS EarliestDateOfAnotherThing
FROM
    TableA TblA
WHERE
    TblA.SomeField = 'Something'
    AND TblA.SomeFieldDate BETWEEN TO_DATE('2015-01-01','YYYY-MM-DD') AND TO_DATE('2015-12-31','YYYY-MM-DD')

然而，除此之外，我想包括另一个计算列，它根据 EarliestDateOfAnotherThing 的实际情况返回文本输出。我可以使用 CASE WHEN 语句执行此操作，如下所示:

CASE WHEN
    (
    SELECT
        MIN(TblAAlias.SomeFieldDate)
    FROM
        TableA TblAAlias
    WHERE
        TblAAlias.ID = TblA.ID /* Here is the link reference to the main query */
        TblAAlias.SomeField = 'Another Thing'
        AND TblAAlias.SomeFieldDate <= TblA.SomeFieldDate /* Another link reference */
    ) BETWEEN TO_DATE('2000-01-01','YYYY-MM-DD') AND TO_DATE('2004-12-31','YYYY-MM-DD')
    THEN 'First period'
    WHEN
    (
    SELECT
        MIN(TblAAlias.SomeFieldDate)
    FROM
        TableA TblAAlias
    WHERE
        TblAAlias.ID = TblA.ID /* Here is the link reference to the main query */
        TblAAlias.SomeField = 'Another Thing'
        AND TblAAlias.SomeFieldDate <= TblA.SomeFieldDate /* Another link reference */
    ) BETWEEN TO_DATE('2005-01-01','YYYY-MM-DD') AND TO_DATE('2009-12-31','YYYY-MM-DD')
    THEN 'Second period'
    ELSE 'Last period'
END

这一切都很好。然而问题是我正在重新运行完全相同的子查询 - 这让我觉得效率很低。我想做的是只运行一次子查询，然后获取输出并将其置于各种情况下。就像我可以使用 VBA 语句“SELECT CASE”一样:

''''' Note that this is pseudo-VBA not SQL:
Select case (Subquery which returns a date)
    Case Between A and B
        "Output 1"
    Case Between C and D
        "Output 2"
    Case Between E and F
        "Output 3"
End select
' ... etc

我的调查表明 SQL 语句“DECODE”可以完成这项工作:但是事实证明 DECODE 仅适用于离散值，而不适用于日期范围。我还发现了一些关于将子查询放在 FROM 部分中的事情 - 然后在 SELECT 的多个地方重新使用输出。然而，这失败了，因为子查询本身并没有站起来，而是依赖于将值与主查询进行比较……并且在执行主查询之前无法进行这些比较(因此进行循环引用，因为FROM 部分本身就是主查询的一部分)。

如果有人能告诉我一种实现我想要的简单方法，我将不胜感激 - 因为到目前为止，唯一有效的方法是在我想要的每个地方手动重新使用子查询代码，但作为程序员，我很痛苦这么低效!

编辑:
感谢您到目前为止的答案。但是我想我将不得不在这里粘贴真实的、未简化的代码。我试图简化它以明确概念，并删除潜在的识别信息 - 但到目前为止的答案清楚地表明它比我的基本 SQL 知识所允许的更复杂。我试图围绕人们给出的建议进行思考，但我无法将这些概念与我的实际代码相匹配。例如，我的实际代码包含多个表，我在主查询中从中选择。

我想我将不得不硬着头皮展示我的(仍然是简化的，但更准确的)实际代码，在这些代码中我一直试图让“从子句中的子查询”工作。也许好心的人能够使用它来更准确地指导我如何使用目前在我的实际代码中引入的概念？谢谢。

SELECT
    APPLICANT.ID,
    APPLICANT.FULL_NAME,
    EarliestDate,
    CASE
        WHEN EarliestDate BETWEEN TO_DATE('2000-01-01','YYYY-MM-DD') AND TO_DATE('2004-12-31','YYYY-MM-DD') THEN 'First Period'
        WHEN EarliestDate BETWEEN TO_DATE('2005-01-01','YYYY-MM-DD') AND TO_DATE('2009-12-31','YYYY-MM-DD') THEN 'Second Period'
        WHEN EarliestDate >= TO_DATE('2010-01-01','YYYY-MM-DD') THEN 'Third Period'
    END
FROM
    /* Subquery in FROM - trying to get this to work */
    (
    SELECT
        MIN(PERSON_EVENTS_Sub.REQUESTED_DTE) /* Earliest date of the secondary event */
    FROM
        EVENTS PERSON_EVENTS_Sub
    WHERE
        PERSON_EVENTS_Sub.PER_ID = APPLICANT.ID /* Link the person ID */
        AND PERSON_EVENTS_Sub.DEL_IND IS NULL /* Not a deleted event */
        AND PERSON_EVENTS_Sub.EVTYPE_SDV_VALUE IN (/* List of secondary events */)
        AND PERSON_EVENTS_Sub.COU_SDV_VALUE = PERSON_EVENTS.COU_SDV_VALUE /* Another link from the subQ to the main query */
        AND PERSON_EVENTS_Sub.REQUESTED_DTE <= PERSON_EVENTS.REQUESTED_DTE /* subQ event occurred before main query event */
        AND ROWNUM = 1 /* To ensure only one record returned, in case multiple rows match the MIN date */
    ) /* And here - how would I alias the result of this subquery as "EarliestDate", for use above? */,
    /* Then there are other tables from which to select */
    EVENTS PERSON_EVENTS,
    PEOPLE APPLICANT
WHERE
    PERSON_EVENTS.PER_ID=APPLICANT.ID
    AND PERSON_EVENTS.EVTYPE_SDV_VALUE IN (/* List of values - removed ID information */)
    AND PERSON_EVENTS.REQUESTED_DTE BETWEEN '01-Jan-2014' AND '31-Jan-2014'

Answer 1

看 只有在重构现有查询时(而不是逻辑或功能上不同的方法)。

对我来说，最简单的方法就是将其作为嵌套查询执行...
- 内部查询将是您的基本查询，没有 CASE 语句
- 它还包括您的相关子查询作为附加 领域
- 然后外部查询可以将该字段嵌入到 CASE 语句中

SELECT
    nested_query.ID,
    nested_query.FULL_NAME,
    nested_query.EarliestDate,
    CASE
        WHEN nested_query.EarliestDate BETWEEN TO_DATE('2000-01-01','YYYY-MM-DD') AND TO_DATE('2004-12-31','YYYY-MM-DD') THEN 'First Period'
        WHEN nested_query.EarliestDate BETWEEN TO_DATE('2005-01-01','YYYY-MM-DD') AND TO_DATE('2009-12-31','YYYY-MM-DD') THEN 'Second Period'
        WHEN nested_query.EarliestDate >= TO_DATE('2010-01-01','YYYY-MM-DD') THEN 'Third Period'
    END   AS CaseStatementResult
FROM
(
    SELECT
        APPLICANT.ID,
        APPLICANT.FULL_NAME,
        (
        SELECT
            MIN(PERSON_EVENTS_Sub.REQUESTED_DTE) /* Earliest date of the secondary event */
        FROM
            EVENTS PERSON_EVENTS_Sub
        WHERE
            PERSON_EVENTS_Sub.PER_ID = APPLICANT.ID /* Link the person ID */
            AND PERSON_EVENTS_Sub.DEL_IND IS NULL /* Not a deleted event */
            AND PERSON_EVENTS_Sub.EVTYPE_SDV_VALUE IN (/* List of secondary events */)
            AND PERSON_EVENTS_Sub.COU_SDV_VALUE = PERSON_EVENTS.COU_SDV_VALUE /* Another link from the subQ to the main query */
            AND PERSON_EVENTS_Sub.REQUESTED_DTE <= PERSON_EVENTS.REQUESTED_DTE /* subQ event occurred before main query event */
            AND ROWNUM = 1 /* To ensure only one record returned, in case multiple rows match the MIN date */
        )
            AS EarliestDate
    FROM
        EVENTS PERSON_EVENTS,
        PEOPLE APPLICANT
    WHERE
        PERSON_EVENTS.PER_ID=APPLICANT.ID
        AND PERSON_EVENTS.EVTYPE_SDV_VALUE IN (/* List of values - removed ID information */)
        AND PERSON_EVENTS.REQUESTED_DTE BETWEEN '01-Jan-2014' AND '31-Jan-2014'
)   nested_query