当减去2个日期并且差值大于某个数字时，条件不起作用

Question

我给出了下面的where子句，但无论它是否大于4，查询都会给出输出。你能告诉我出了什么问题吗

where to_number(
    end_date - (case when start_date > to_date('01-01-2014','dd-mm-yyyy') 
    then start_date 
    else to_date('01-01-2014','dd-mm-yyyy') end)
) > 4

Answer 1

您的查询在Oracle11和Oracle12:中都适用

with data (start_date, end_date)
  as (
    select  to_date('31-01-2014','dd-mm-yyyy') as start_date,  to_date('05-02-2014','dd-mm-yyyy') as end_date from dual
    union all 
    select  to_date('31-12-2013','dd-mm-yyyy') as start_date,  to_date('01-01-2014','dd-mm-yyyy') as end_date from dual
    union all 
    select  to_date('31-12-2013','dd-mm-yyyy') as start_date,  to_date('01-05-2014','dd-mm-yyyy') as end_date from dual
    union all 
    select  to_date('31-10-2014','dd-mm-yyyy') as start_date,  to_date('15-11-2014','dd-mm-yyyy') as end_date from dual
    union all 
    select  to_date('01-10-2014','dd-mm-yyyy') as start_date,  to_date('06-10-2014','dd-mm-yyyy') as end_date from dual
    union all 
    select  to_date('01-10-2014','dd-mm-yyyy') as start_date,  to_date('05-10-2014','dd-mm-yyyy') as end_date from dual
    union all 
    select  to_date('01-10-2014','dd-mm-yyyy') as start_date,  to_date('04-10-2014','dd-mm-yyyy') as end_date from dual
    union all 
    select  to_date('01-10-2014','dd-mm-yyyy') as start_date,  to_date('03-10-2014','dd-mm-yyyy') as end_date from dual
    )
select start_date, end_date from data
where to_number(
    end_date - (case when start_date > to_date('01-01-2014','dd-mm-yyyy') 
    then start_date 
    else to_date('01-01-2014','dd-mm-yyyy') end)
) > 4

"START_DATE"    "END_DATE"
31-JAN-14   05-FEB-14
31-DEC-13   01-MAY-14
31-OCT-14   15-NOV-14
01-OCT-14   06-OCT-14

请注意，选择了4行数据。我建议在您的查询中添加一个与您的条件相匹配的列，看看发生了什么；以下是显示条件值的查询：

with data (start_date, end_date)
  as (
    select  to_date('31-01-2014','dd-mm-yyyy') as start_date,  to_date('05-02-2014','dd-mm-yyyy') as end_date from dual
    union all 
    select  to_date('31-12-2013','dd-mm-yyyy') as start_date,  to_date('01-01-2014','dd-mm-yyyy') as end_date from dual
    union all 
    select  to_date('31-12-2013','dd-mm-yyyy') as start_date,  to_date('01-05-2014','dd-mm-yyyy') as end_date from dual
    union all 
    select  to_date('31-10-2014','dd-mm-yyyy') as start_date,  to_date('15-11-2014','dd-mm-yyyy') as end_date from dual
    union all 
    select  to_date('01-10-2014','dd-mm-yyyy') as start_date,  to_date('06-10-2014','dd-mm-yyyy') as end_date from dual
    union all 
    select  to_date('01-10-2014','dd-mm-yyyy') as start_date,  to_date('05-10-2014','dd-mm-yyyy') as end_date from dual
    union all 
    select  to_date('01-10-2014','dd-mm-yyyy') as start_date,  to_date('04-10-2014','dd-mm-yyyy') as end_date from dual
    union all 
    select  to_date('01-10-2014','dd-mm-yyyy') as start_date,  to_date('03-10-2014','dd-mm-yyyy') as end_date from dual
    )
select start_date, end_date,
 to_number(
    end_date - (case when start_date > to_date('01-01-2014','dd-mm-yyyy') 
    then start_date 
    else to_date('01-01-2014','dd-mm-yyyy') end)) as elapsed
from data
where to_number(
    end_date - (case when start_date > to_date('01-01-2014','dd-mm-yyyy') 
    then start_date 
    else to_date('01-01-2014','dd-mm-yyyy') end)
) > 4

"START_DATE"  "END_DATE"  "ELAPSED"
31-JAN-14     05-FEB-14          5
31-DEC-13     01-MAY-14        120
31-OCT-14     15-NOV-14         15
01-OCT-14     06-OCT-14          5

为了确保相反的标准有效，我将标准更改为

where to_number(
    end_date - (case when start_date > to_date('01-01-2014','dd-mm-yyyy') 
    then start_date 
    else to_date('01-01-2014','dd-mm-yyyy') end)
) <= 4  

并得到了这个：

"START_DATE"    "END_DATE"  "ELAPSED"
31-DEC-13       01-JAN-14          0
01-OCT-14       05-OCT-14          4
01-OCT-14       04-OCT-14          3
01-OCT-14       03-OCT-14          2