如何从Oracle数据库中计算和选择年龄在25岁以上的员工？ [重复]

This question already has an answer here:

这个问题在这里已有答案:

Oracle Age calculation from Date of birth and Today 9 answers

Oracle Age计算自出生日期和今天的9个答案

We have employees of all ages in the database. We need to select only those employees whose age is more than 25 years 2 months.

我们在数据库中拥有所有年龄段的员工。我们只需要选择那些年龄超过25岁2个月的员工。

3 个解决方案

#1

this will work:

这将工作:

SELECT * FROM emp where sysdate-birthdate>=(SELECT sysdate- 
to_date('19931015','YYYYMMDD') FROM dual);

#2

ADD_MONTHS might help, e.g.

ADD_MONTHS可能有所帮助,例如

select *
from employees
where date_of_birth > add_months(trunc(sysdate), -(25 * 12 + 2));

ADD_MONTHS adds number of months to the first parameter

ADD_MONTHS为第一个参数添加月数

TRUNC(SYSDATE) returns today's day at midnight; dates of birth usually don't have a time component.

TRUNC(SYSDATE)今天午夜返回;出生日期通常没有时间成分。

the second parameter is:
- -, because we have to subtract months ("25 years ago")
- 25 - 25 years
- 12 - a year has 12 months
- + 2 add 2 months

第二个参数是: - ,因为我们必须减去月份(“25年前”)25 - 25年12 - 一年有12个月+ 2加2个月

#3

you could use the months_between function https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions089.htm

你可以使用months_between函数https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions089.htm

Oracles own example:

神谕自己的榜样:

 SELECT MONTHS_BETWEEN     (TO_DATE('02-02-1995','MM-DD-YYYY'),
     TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"
     FROM DUAL;

Months

1.03225806

your query would be something like

你的查询会是这样的

select * from emp where MONTHS_BETWEEN (sysdate, date_of_birth) > ((25*12)+2)

#1