Oracle SQL：一再行使CASE WHEN的子查询,而不必一再子查询

我有一个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常识应承的更伟大.我试图环绕人们给出的提议,但我无法将观念与我的现实代码相匹配.譬喻,我的现实代码包罗我在主查询中选择的多个表.

我想我将不得不咬紧牙关并展示我的(如故简化但更精确)现实代码,个中我一向试图让“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'

办理要领

（编辑：湖南网）

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