在case when语句中使用计算列结果

Question

我使用的是Oracle SQL Developer，我有一个语句

select 
c.name as "Customer Name"
cy.country as "Country",
c.startdateutc as "UTC Start Date"
case when c.startdateutc >= To_date('2023-03-12 08:00', 'YYYY-MM-DD hh24:mi') and c.startdateutc <= To_date('2023-11-05 07:00', 'YYYY-MM-DD hh24:mi') then c.startdateutc  - 5/24
else c.startdateutc - 6/24 END AS "New Start Date"
from
customer c
left join country cy
on cy.id = c.countryid
where c.startdateutc >= date '2020-07-01' 

我想创建另一个类似于的基于案例时列的列

case when New Start Date >= To_date('2023-03-12 08:00', 'YYYY-MM-DD hh24:mi') and New Start Date <= To_date('2023-11-05 07:00', 'YYYY-MM-DD hh24:mi') then New Start Date  - 5/24
else New Start Date - 6/24 END AS "New Calc Start Date"

提前感谢

Answer 1

一个选项是嵌套CASEs:

select 
case when 
case when     c.startdateutc >= To_date('2023-03-12 08:00', 'YYYY-MM-DD hh24:mi') 
and c.startdateutc <= To_date('2023-11-05 07:00', 'YYYY-MM-DD hh24:mi') 
then c.startdateutc  - 5/24
else c.startdateutc - 6/24 
END 
then something
else something_else
end new_case_column
from ...

---

另一种是将当前查询用作子查询(或CTE(，例如

with current_query as
(select 
c.name as "Customer Name"
cy.country as "Country",
c.startdateutc as "UTC Start Date"
case when c.startdateutc >= To_date('2023-03-12 08:00', 'YYYY-MM-DD hh24:mi') and    
c.startdateutc <= To_date('2023-11-05 07:00', 'YYYY-MM-DD hh24:mi') then 
c.startdateutc  - 5/24
else c.startdateutc - 6/24 
END AS new_start_date
from
customer c
left join country cy
on cy.id = c.countryid
where c.startdateutc >= date '2020-07-01' 
)
select case when new_start_date = something then something
else something else
end
from current_query join ...

Answer 2

考虑使用这样的子查询：

SELECT COLUMNNAMES
FROM (
SELECT COLUMNNAMES
FROM TABLEname2
WHERE condition
) t

在这种情况下：

SELECT 
t.*
,CASE 
WHEN New_Start_Date >= To_date('2023-03-12 08:00', 'YYYY-MM-DD hh24:mi')
AND New_Start_Date <= To_date('2023-11-05 07:00', 'YYYY-MM-DD hh24:mi')
THEN New_Start_Date - 5 / 24
ELSE New_Start_Date - 6 / 24
END AS "New Calc Start Date"
FROM (
SELECT c.name AS "Customer Name"
,cy.country AS "Country"
,c.startdateutc AS "UTC Start Date" 
,CASE 
WHEN c.startdateutc >= To_date('2023-03-12 08:00', 'YYYY-MM-DD hh24:mi')
AND c.startdateutc <= To_date('2023-11-05 07:00', 'YYYY-MM-DD hh24:mi')
THEN c.startdateutc - 5 / 24
ELSE c.startdateutc - 6 / 24
END AS "New_Start_Date"
FROM customer c
LEFT JOIN country cy ON cy.id = c.countryid
WHERE c.startdateutc >= DATE '2020-07-01'
) t