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; 我自己想出的答案太复杂了 求教更简单的方法 谢谢
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; 我自己想出的答案太复杂了 求教更简单的方法 谢谢
1 显示只有首字母大写的所有雇员姓名 select ename from emp where substr(ename,0,1) between 'A' and 'Z';
ceil(sysdate-hiredate)/30 as 月,
ceil(sysdate-hiredate) as 日
from emp
1 显示只有首字母大写的所有雇员姓名 select ename from emp where upper(substr(ename,0,1))
select ename from emp where
substr(ename,0,1)=upper(substr(ename,0,1))
and substr(ename,2)=lower(substr(ename,2))
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
比如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')
对'日'进行下修正
结果如下
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
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
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天,若算成一个月就是这个语句,否则就是上面刚才发的那个。