Oracle SQL:重用 CASE WHEN 的子查询，而不必重复子查询

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 语句"选择大小写"一样，如下所示：

''''' 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') 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

Answer 2

您可以使用分析函数（以及在单个表扫描中）在没有相关子查询或子查询分解 （ WITH .. AS ( ... ) ） 子句的情况下执行此操作：

SELECT ID,
       EarliestDateOfAnotherThing
FROM   (
  SELECT ID,
         MIN( CASE WHEN SomeField = 'Another Thing' THEN SomeFieldDate END )
           OVER( PARTITION BY ID
                 ORDER BY     SomeFieldDate
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
           AS EarliestDateOfAnotherThing
  FROM  TableA
)
WHERE SomeField = 'Something'
AND   SomeFieldDate BETWEEN TO_DATE('2015-01-01','YYYY-MM-DD')
                        AND TO_DATE('2015-12-31','YYYY-MM-DD')

您可以将扩展案例示例设置为：

SELECT ID,
       CASE
         WHEN DATE '2000-01-01' <= EarliestDateOfAnotherThing
              AND EarliestDateOfAnotherThing < DATE '2005-01-01'
         THEN 'First Period'
         WHEN DATE '2005-01-01' <= EarliestDateOfAnotherThing
              AND EarliestDateOfAnotherThing < DATE '2010-01-01'
         THEN 'Second Period'
         ELSE 'Last Period'
       END AS period
FROM   (
  SELECT ID,
         MIN( CASE WHEN SomeField = 'Another Thing' THEN SomeFieldDate END )
           OVER( PARTITION BY ID
                 ORDER BY     SomeFieldDate
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
           AS EarliestDateOfAnotherThing
  FROM  TableA
)
WHERE SomeField = 'Something'
AND   SomeFieldDate BETWEEN TO_DATE('2015-01-01','YYYY-MM-DD')
                        AND TO_DATE('2015-12-31','YYYY-MM-DD')

Answer 3

您没有在 FROM 子句中提供子查询，这本来很有用，因为这是可以完成的一种方法：

SELECT
    TblA.ID,
    ED.MinSomeFieldDate,
    CASE...
FROM
    TableA A
LEFT OUTER JOIN
(
    SELECT
        SQ_A.Id,
        MIN(SQ_A.SomeFieldDate) AS MinSomeFieldDate
    FROM
        TableA SQ_A
    WHERE
        SQ_A.SomeField = 'Another Thing'
    GROUP BY
        SQ_A.Id
) AS ED ON
    ED.Id = A.Id AND
    ED.MinSomeFieldDate <= A.SomeFieldDate  -- We can do this outside of the subquery since it's MIN and <=
WHERE
    A.SomeField = 'Something' AND
    A.SomeFieldDate BETWEEN TO_DATE('2015-01-01','YYYY-MM-DD') AND TO_DATE('2015-12-31','YYYY-MM-DD')

Answer 4

Oracle 可能足够聪明，可以优化这两个子查询，但为什么要麻烦呢？ 我认为使用 CTE 更清楚地编写查询：

with q as (
      <your query here>
     )
select q.*,
       (case . . . 
        end) as another_calculated_column
from q;

这是一般结构。 您可能需要在逻辑的q中包含其他列。

Answer 5

除了其他答案之外，您还可以将查询包装在外部查询中，并在外部查询中使用 case 语句，如下所示：

select id,
       case when earliestdateofanotherthing between a and b then 'Output 1a'
            when earliestdateofanotherthing between c and d then 'Output 2a'
            when earliestdateofanotherthing between e and f then 'Output 3a'
            else 'default clause output - a' -- null if excluded
       end some_col1,
       case when earliestdateofanotherthing between a and b then 'Output 1b'
            when earliestdateofanotherthing between c and d then 'Output 2b'
            when earliestdateofanotherthing between e and f then 'Output 3b'
            else 'default clause output - b' -- null if excluded
       end some_col2
from  (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 */
               and 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'));

或者，您可以使用子查询分解（也称为公用表表达式 （CTE）/WITH 子句）将主查询拉入子查询，然后从中进行选择：

with main_qry as (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 */
                          and 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'))
select id,
       case when earliestdateofanotherthing between a and b then 'Output 1a'
            when earliestdateofanotherthing between c and d then 'Output 2a'
            when earliestdateofanotherthing between e and f then 'Output 3a'
            else 'default clause output - a' -- null if excluded
       end some_col1,
       case when earliestdateofanotherthing between a and b then 'Output 1b'
            when earliestdateofanotherthing between c and d then 'Output 2b'
            when earliestdateofanotherthing between e and f then 'Output 3b'
            else 'default clause output - b' -- null if excluded
       end some_col2
from   main_qry;

将子查询保留在 select 子句中的优点是，假设存在适当的索引，则可以从子查询缓存中受益。它的性能可能与@MT0使用分析函数完成查找earliestdateofanotherthing列的工作的解决方案相同或更高;您需要针对数据和表结构测试这两种解决方案，以确定哪一个是最好的。

（注我怀疑@MT0的解决方案将是最好的解决方案;我主要把这个答案作为一个例子，说明如何重用一列而不必计算两次。

---

关于您问题中的更新查询，这可能会完成您所追求的：

with main_qry as (SELECT
                      APPLICANT.ID,
                      APPLICANT.FULL_NAME,
                      case when min(case when person_events.del_ind is null
                                              and evtype_sdv_value in (/* List of secondary events */)
                                              then person_events.REQUESTED_DTE
                                    end) over (partition by person_events.per_id, person_events.cou_sdv_value) <= person_events.requested_dte then
                               min(case when person_events.del_ind is null
                                             and evtype_sdv_value in (/* List of secondary events */)
                                             then person_events.REQUESTED_DTE
                                   end) over (partition by person_events.per_id, person_events.cou_sdv_value)
                      end earliest_date
                  FROM
                      EVENTS PERSON_EVENTS,
                      inner join PEOPLE APPLICANT on (PERSON_EVENTS.PER_ID=APPLICANT.ID)
                  WHERE 
                      PERSON_EVENTS.EVTYPE_SDV_VALUE IN (/* List of values - removed ID information */)
                      AND PERSON_EVENTS.REQUESTED_DTE BETWEEN to_date('01-Jan-2014', 'dd-mm-yyyy') AND to_date('31-Jan-2014', 'dd-mm-yyyy'))
select id,
       full_name,
       earliest_date,
       CASE
           WHEN EarliestDate >= TO_DATE('2010-01-01','YYYY-MM-DD') THEN 'Third Period'
           WHEN EarliestDate >= TO_DATE('2005-01-01','YYYY-MM-DD') THEN 'Second Period'
           WHEN EarliestDate >= TO_DATE('2000-01-01','YYYY-MM-DD') THEN 'First Period'
       END period_type
from   main_qry;

显然，您必须对其进行测试！

请注意：

1. 我使用 to_date 在 main_qry 的 where 子句中添加了字符串的显式转换作为日期;依赖隐式日期转换不是一个好主意，尤其是在生产代码中！NLS_DATE_FORMAT参数可以轻松更改，这将导致难以识别错误！
2. 我修改了您的案例陈述，以考虑到您的 最早日期字段可能落在裂缝之间的情况（例如，如果它的日期为"31/12/2004 13：03：23"）。它还使它更容易阅读！

Answer 6

这是一个

lateral的解决方案。我已经对您的表重新排序，并使用 ANSI 联接语法进行内部联接。此外，我从未真正在 Oracle 上编写过lateral或cross apply查询，我注意到这主要是因为我可能犯了一个小的语法错误，而且我略读的文档并没有让我清楚是否有区别。

虽然我认为将其转换为仅使用另一个内部连接的形式并不困难，但我确实认为这是您在提出问题时正在寻找的概念。

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
    EVENTS PERSON_EVENTS inner join PEOPLE APPLICANT
        on APPLICANT.ID = PERSON_EVENTS.PER_ID
    LATERAL /* or possibly just CROSS APPLY */
    (
        SELECT
            MIN(PERSON_EVENTS_Sub.REQUESTED_DTE) EarliestDate
        FROM
            EVENTS PERSON_EVENTS_Sub
        WHERE
            PERSON_EVENTS_Sub.PER_ID = APPLICANT.ID
            AND PERSON_EVENTS_Sub.DEL_IND IS NULL
            AND PERSON_EVENTS_Sub.EVTYPE_SDV_VALUE IN (...)
            AND PERSON_EVENTS_Sub.COU_SDV_VALUE = PERSON_EVENTS.COU_SDV_VALUE
            AND PERSON_EVENTS_Sub.REQUESTED_DTE <= PERSON_EVENTS.REQUESTED_DTE
    ) /* I don't think you need an alias here? */
WHERE
        PERSON_EVENTS.EVTYPE_SDV_VALUE IN (...)
    AND PERSON_EVENTS.REQUESTED_DTE BETWEEN '01-Jan-2014' AND '31-Jan-2014'

Answer 7

好的伙计们，非常感谢你们的帮助。我可以理解其中的一些 - 其他部分让我头晕目眩。我是一个SQL初学者，所以我相信我会及时学习这些更复杂的概念。但是，我现在设法做的是使用答案中向我建议的概念来解决我的问题，但仍然使用我熟悉的简单直接的代码。

基本上，我没有将日期输出子查询作为 CTE，

而是将整个主要原始查询作为 CTE（自然是独立的）;然后通过在进一步的查询中使用该 CTE，我能够根据需要操作该日期列。如下：

WITH MAIN_QUERY AS
    (
    SELECT
        APPLICANT.ID,
        APPLICANT.FULL_NAME,
        (ltrim(decode(to_char(APPLICANT.DOB_DD)||'/'||to_char(APPLICANT.DOB_MM)||'/'||to_char(APPLICANT.DOB_YYYY),'//',null,lpad(to_char(APPLICANT.DOB_DD),2,'0')||'/'||lpad(to_char(APPLICANT.DOB_MM),2,'0')||'/'||to_char(APPLICANT.DOB_YYYY)),'/')) AS DOB,
        PERSON_EVENTS.REQUESTED_DTE,
        PERSON_EVENTS.COU_SDV_VALUE,
        /* Find the date of EARLIEST secondary event */
        (
        SELECT
            MIN(PERSON_EVENTS3.REQUESTED_DTE)
        FROM
            EVENTS PERSON_EVENTS3
        WHERE
            PERSON_EVENTS3.PER_ID=APPLICANT.ID /* Ensure same person ID as main query */
            AND PERSON_EVENTS3.DEL_IND IS NULL
            AND PERSON_EVENTS3.EVTYPE_SDV_VALUE IN (/* Secondary event values */)
            AND ROWNUM = 1
        ) AS EarliestDate /* Alias this subquery to a column name, for use later on */
    FROM
        EVENTS PERSON_EVENTS,
        PEOPLE APPLICANT
    WHERE
        PERSON_EVENTS.PER_ID(+)=APPLICANT.ID
        AND PERSON_EVENTS.EVTYPE_SDV_VALUE IN (/* Primary event values */)
        AND PERSON_EVENTS.REQUESTED_DTE BETWEEN TO_DATE('2010-01-01','YYYY-MM-DD') AND TO_DATE('2011-01-01','YYYY-MM-DD')
    )
SELECT
    ID,
    FULL_NAME,
    DOB,
    REQUESTED_DTE,
    COU_SDV_VALUE,
    EarliestDate, /* Use the date by alias - this works */
    /* Then the alias can be further used how I like, without having to re-run the subquery :) */
    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 MAIN_QUERY

所以 - 再次感谢大家。我在这里发布这个解决方案作为我的首选解决方案（考虑到我的SQL专业知识水平），但如果没有你的帮助，我将无法做到这一点。我希望这个线程对其他任何希望能够重用子查询结果而不必重复它的人有用。