Question

Oracle 根据出生日期计算年龄

select
(c.name)姓名,
(select (to_char(sysdate,'YYYY')-to_char(c.birthdate, 'YYYY')) age from C c)年龄,
(b.P_NAME)项目名称,
(a.NUMBER)流水号
from
A a,
B b,
C c
where
a.id=b.id
and
a.id=c.id
说明： c.birthdate是date类型，‘YYYY-MM-DD 00:00:00'
以上提示单行查询语句返回多行

Answer 1

可用to_char函数将date类型转成字符类型。

如emp表中有如下数据：

如果计算生日的方法是当前日期的年份减去生日的年份，可用如下语句：

select ename,hiredate,to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy') from emp;

查询结果：

Answer 2

肯定的，你的子查询写，要么是写多了，要么是写少了。
写多了，可以不用写子查询的
(select (to_char(sysdate,'YYYY')-to_char(c.birthdate, 'YYYY')) age from C c)年龄,
改为
to_char(sysdate,'YYYY')-to_char(c.birthdate, 'YYYY') 年龄即可
写少了.(先确定id在c表中没有重复啊。)
from中去掉c
,然后在
(select (to_char(sysdate,'YYYY')-to_char(c.birthdate, 'YYYY')) age from C c)年龄,
改为
(select (to_char(sysdate,'YYYY')-to_char(c.birthdate, 'YYYY')) age from C c where c.id=a.id)年龄,

Answer 3

试试看子查询加上rownum=1
select
(c.name)姓名,
(select (to_char(sysdate,'YYYY')-to_char(c.birthdate, 'YYYY')) age from C c where rownum=1)年龄,
(b.P_NAME)项目名称,
(a.NUMBER)流水号
from
A a,
B b,
C c
where
a.id=b.id
and
a.id=c.id

Answer 4

把select换成ceil( months_between(sysdate,c.birthdate)/12)

Answer 5

months_between(trunc(sysdate,'yyyy'),trunc(c.birthdate,'yyyy'))/12