从两个日期计算年份

Question

我在oracle数据库上运行。我想找到所有在25岁前接受治疗的受助者。我知道至少有500个，但我得到的结果是0.显然，我的查询是错误的。所以我认为问题在于我减去日期的方式。他们都是表格中的日期类型。从我看到的情况来看，应该可以只减去两个日期？ 有人写道他们使用DATEDIFF，但这对我不起作用。

select count(*) FROM V_Persons 
    where exists(select * from res_tx res, recipient rec, Treatment tr 
       where (tr.TREAT_DATE - rec.DATE_OF_BIRTH) < 25 
       and rec.ident = res.ident 
       and rec.ident = tr.ident) 

Answer 1

如果日期差异正如您期望的那样在存在子查询中的查询中工作，请尝试此操作来排除故障。

select * , (tr.TREAT_DATE - rec.DATE_OF_BIRTH) as DateDifference 
from res_tx res, recipient rec, Treatment tr 
       where (tr.TREAT_DATE - rec.DATE_OF_BIRTH) < 25 
       and rec.ident = res.ident 
       and rec.ident = tr.ident 

Answer 2

SELECT COUNT(*) 
FROM V_Persons p 
WHERE EXISTS(
    SELECT 'X' 
    FROM recipient c 
     INNER JOIN Treatment r 
     ON (c.ident = r.ident) 
     INNER JOIN res_tx s  -- Do you need to join this table? 
     ON (c.ident = s.ident) 
    WHERE r.TREAT_DATE < ADD_MONTHS(c.DATE_OF_BIRTH, 25 * 12) 
    AND p.ident = c.ident   -- You need to correlate with the outer query. 
);