Oracle SQL：重复使用CASE WHEN的子查询,而不必重复子查询

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知识允许的更复杂.我试图围绕人们给出的建议,但我无法将概念与我的实际代码相匹配.例如,我的实际代码包括我在主查询中选择的多个表.

我想我将不得不咬紧牙关并展示我的(仍然简化但更准确)实际代码,其中我一直试图让“FROM子句中的子查询”工作起作用.也许某些人能够使用它来更准确地指导我如何使用到目前为止在我的实际代码中引入的概念？谢谢.

SELECT
    APPLICANT.ID,APPLICANT.FULL_NAME,EarliestDate,CASE
        WHEN EarliestDate BETWEEN TO_DATE('2000-01-01','YYYY-MM-DD') THEN 'First Period'
        WHEN EarliestDate BETWEEN TO_DATE('2005-01-01','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'

解决方法

对我来说,最简单的方法就是将其作为嵌套查询…
– 内部查询将是您的基本查询,没有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') THEN 'First Period'
        WHEN nested_query.EarliestDate BETWEEN TO_DATE('2005-01-01','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,(
        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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!