Oracle的表中一个字段是日期型字段,我想用这个字段求年龄,sql语句
select name,cssj,telephone,hykh,jfsyje,trunc(months_between(sysdate,cssj)/12) as age from MD_KH
成功地返回了年龄,但我想对年龄进行排序,如只返回年龄45岁以上的
select name,cssj,telephone,hykh,jfsyje,trunc(months_between(sysdate,cssj)/12) as age from MD_KH
where age>=45
但这条语句就报错,错误代码是ORA-00904
为什么,应该怎样写?
select name,cssj,telephone,hykh,jfsyje,trunc(months_between(sysdate,cssj)/12) as age from MD_KH
成功地返回了年龄,但我想对年龄进行排序,如只返回年龄45岁以上的
select name,cssj,telephone,hykh,jfsyje,trunc(months_between(sysdate,cssj)/12) as age from MD_KH
where age>=45
但这条语句就报错,错误代码是ORA-00904
为什么,应该怎样写?
SELECT NAME
,CSSJ
,TELEPHONE
,HYKH
,JFSYJE
,TRUNC(MONTHS_BETWEEN(SYSDATE, CSSJ) / 12) AS AGE
FROM MD_KH
WHERE TRUNC(MONTHS_BETWEEN(SYSDATE, CSSJ) / 12) >= 45
where trunc(months_between(sysdate,cssj)/12)>=45
trunc(months_between(sysdate,cssj)/12) as age from MD_KH
where trunc(months_between(sysdate,cssj)/12)>=45
,CSSJ
,TELEPHONE
,HYKH
,JFSYJE
,TRUNC(MONTHS_BETWEEN(SYSDATE, CSSJ) / 12) AS AGE
FROM MD_KH
WHERE TRUNC(MONTHS_BETWEEN(SYSDATE, CSSJ) / 12) >= 45在这里 age是别名,不能使用,除非在嵌套一层
现在你的where 语句引用了输出列的东西,肯定报错哈。。
WHERE TRUNC(MONTHS_BETWEEN(SYSDATE,CSSJ))>=45*12
据说在=左边进行计算会影响查询速度?
select name,cssj,telephone,hykh,jfsyje,trunc(months_between(sysdate,cssj)/12) as "age" from MD_KH
where age>=45加个引号 试试
,CSSJ
,TELEPHONE
,HYKH
,JFSYJE
,TRUNC(MONTHS_BETWEEN(SYSDATE, CSSJ) / 12) AS AGE
FROM MD_KH
WHERE TRUNC(MONTHS_BETWEEN(SYSDATE, CSSJ) / 12) >= 45在这里 age是别名,不能使用,除非在嵌套一层