请教 两个 emp表 中查询问题

Question

emp表
1 显示只有首字母大写的所有雇员姓名select ename from emp where substr(ename,0,1) in('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
2 以年，月和日显示所有员工的服务年限select ename,floor(months_between(sysdate,hiredate)/12) 年,(floor(months_between(sysdate,hiredate))-(floor(months_between(sysdate,hiredate)/12))*12) 月,floor((sysdate-hiredate)-(floor(months_between(sysdate,hiredate)/12)*365)-((floor(months_between(sysdate,hiredate))-(floor(months_between(sysdate,hiredate)/12))*12))*30) 日 from emp;我自己想出的答案太复杂了 求教更简单的方法 谢谢

Answer 1

SELECT SUBSTR(ENAME,1,1)
  FROM EMP
 WHERE ASCII(SUBSTR(ENAME,1,1)) BETWEEN 60 AND 90;

Answer 2

2、select ceil(sysdate-hiredate)/365 as 年 
ceil(sysdate-hiredate)/30 as 月, 
ceil(sysdate-hiredate) as 日 
from emp 
 

Answer 3

select * from t where substr(name,0,1)>='A' and substr(name,0,1)<='Z';
select * from t where name=initcap(name);
select * from t where ascii(substr(name,0,1)) between 65 and 90;

Answer 4

问题重复了
http://topic.csdn.net/u/20090820/18/95b67c71-16c5-4b45-bb7a-311cdf5897cb.html

Answer 5

那个帖子连续回复3次，回不了了
在这里贴出
天数可以用round(mod(months_between(trunc(sysdate-62),trunc(hiredate)),1)*31)
select ename,
       trunc(months_between(sysdate,hiredate)/12) 年,
       mod(trunc(months_between(sysdate,hiredate)),12) 月,
       round(mod(months_between(trunc(sysdate-62),trunc(hiredate)),1)*31) 日
       from emp

Answer 6

应该是：
ELECT SUBSTR(ENAME,1,1)FROM EMPWHEREASCII(SUBSTR(ENAME,1,1))BETWEEN 65 AND 90;

Answer 7

1.‘只有’首字母大写
select ename from emp where 
  substr(ename,0,1)=upper(substr(ename,0,1)) 
  and substr(ename,2)=lower(substr(ename,2))
2.天数上不能简单用365天和30天，这是个不确定的数select ename,
       trunc(months_between(sysdate,hiredate)/12) 年,
       mod(trunc(months_between(sysdate,hiredate)),12) 月,
       mod(to_char(sysdate,'DD')-to_char(hiredate,'DD')+to_char(trunc(sysdate,'MM')-1,'DD'),to_char(trunc(sysdate,'MM')-1,'DD'))日
       from emp

Answer 8

如果是10g,问题一用正则表达式最简单: select ename from emp where regexp_like(ename,'^[A-Z]');问题二,供参考
select 
   ename, 
   TRUNC(work_months/12) "年",
   TRUNC(MOD(work_months,12)) "月",
   TRUNC(MOD(work_months,1)*30) "日"
 from
(select ename, months_between(sysdate, hiredate) work_months from emp);