数据库查询的问题

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

emp表 
1 显示只有首字母大写的所有雇员姓名 select ename from emp where substr(ename,0,1) between 'A' and 'Z';

Answer 2

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

Answer 3

emp表 
1 显示只有首字母大写的所有雇员姓名 select ename from emp where upper(substr(ename,0,1))

Answer 4

错了。看错了。select ename from emp where upper(substr(ename,0,1))这个是显示雇员姓名大写首字母。

Answer 5

1.'只有'首字母大写
select ename from emp where 
  substr(ename,0,1)=upper(substr(ename,0,1)) 
  and substr(ename,2)=lower(substr(ename,2))

Answer 6

2.select ename,
       trunc(months_between(sysdate,hiredate)/12) 年,
       mod(trunc(months_between(sysdate,hiredate)),12) 月,
       mod(to_char(sysdate,'DD')-to_char(hiredate,'DD')+30,30) 日
       from emp

Answer 7

其实用30不是很准确
比如2009年8月20日-2009年2月28日
这个'天'部分应该是20-28+（2009年7月的天数）才对
所以严格说来
30应该替换成
trunc(sysdate,'MM')-to_date(to_char(sysdate,'YYYY')||to_char(sysdate,'MM')-1,'YYYYMM')

Answer 8

我的答案最新版本
对'日'进行下修正
结果如下
select ename,
       trunc(months_between(sysdate,hiredate)/12) y,
       mod(trunc(months_between(sysdate,hiredate)),12) m,
       mod(to_char(sysdate,'DD')-to_char(hiredate,'DD')+to_char(trunc(sysdate,'MM')-1,'DD'),to_char(trunc(sysdate,'MM')-1,'DD'))d
       from emp

Answer 9

create table emp
(ename varchar2(30) primary key,
 hire_date date);
insert into emp values('ZhangSan','2008-02-29');
insert into emp values('LiSi','2007-02-28');
insert into emp values('WangWu','2008-07-03');
insert into emp values('SunLiu','2009-05-31');
insert into emp values('smith','2009-03-12');
insert into emp values('paul','2006-01-28');
insert into emp values('Byant','2007-02-28');SQL> select *from emp;ENAME                          HIREDATE
------------------------------ ----------
ZhangSan                       2008-02-29
LiSi                           2007-02-28
WangWu                         2008-07-03
SunLiu                         2009-05-31
smith                          2009-03-12
paul                           2006-01-28
Byant                          2007-02-28SQL> select ename from emp where substr(ename,0,1) between 'A' and 'Z';ENAME
------------------------------
Byant
LiSi
SunLiu
WangWu
ZhangSan
SQL>select ename,trunc(months_between(sysdate,hiredate)/12) year ,
       mod(trunc(months_between(sysdate,hiredate)),12) month,
       case when to_char(sysdate,'dd')-to_char(hiredate,'dd')>0 then to_char   (sysdate,'dd')-to_char(hiredate,'dd')
            else last_day(hiredate)-hiredate + to_char(sysdate,'dd')
       end   day from emp;
ENAME                                YEAR      MONTH        DAY
------------------------------ ---------- ---------- ----------
ZhangSan                                1          5         21
LiSi                                    2          5         21
WangWu                                  1          1         18
SunLiu                                  0          2         21
smith                                   0          5          9
paul                                    3          6         24
Byant                                   2          5         21

Answer 10

SQL> select ename,trunc(months_between(sysdate,hiredate)/12) year ,
           mod(trunc(months_between(sysdate,hiredate)),12) month,
           case when to_char(sysdate,'dd')-to_char(hiredate,'dd')>0 then to_char(sysdate,'dd')-to_char(hiredate,'dd')
                else to_char(last_day(hiredate+trunc(months_between (sysdate,hiredate)/12)),'dd')-to_char(hiredate,'dd') + to_char(sysdate,'dd')
           end   day from emp;ENAME                                YEAR      MONTH        DAY
------------------------------ ---------- ---------- ----------
ZhangSan                                1          5         23
LiSi                                    2          5         24
WangWu                                  1          1         18
SunLiu                                  0          2         21
smith                                   0          5          9
paul                                    3          6         24
Byant                                   2          5         24不知道你这个月数怎么算一个月，比如2009年2月28到2009年3月28是算成一个月，还是28天，若算成一个月就是这个语句，否则就是上面刚才发的那个。