addweek oracle_oracle的日期运算

能，下面就介绍使用Oracle函数计算时间差的实现方法，如果您对Oracle函数方面感兴趣的话，不妨一看。

两个Date类型字段：START_DATE，END_DATE，计算这两个日期的时间差(分别以天，小时，分钟，秒，毫秒)：

天：

ROUND(TO_NUMBER(END_DATE - START_DATE))

小时：

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)

分钟：

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)

秒：

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)

毫秒：

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 1000)

select * from departments;

create or replace view v_hsd_001 as

select department_id,department_name

from departments;

select * from employees where department_id=80;

select e.employee_id,e.last_name,e.first_name,e.salary

from departments d,employees e

where department_name='Marketing'

order by hire_date asc;

create or replace view v_hsd_002 as

select e.employee_id,e.last_name,e.first_name,e.salary

from departments d,employees e

where d.department_name='Marketing';

select * from v_hsd_002;

create or replace view v_hsd_003 as

select employee_id,last_name,first_name

from employees

where manager_id is null;

select * from v_hsd_003;

create or replace view v_hsd_004 as

select employee_id,last_name,first_name,salary

from employees

where hire_date&gt=to_date('1-1-2000','mm-dd-yyyy');

select * from v_hsd_004;

create or replace view v_hsd_005 as

select employee_id,last_name,first_name,salary

from employees

where last_name like '%D%' or first_name like '%D%';

select * from v_hsd_005;

create or replace view v_hsd_006 as

select count(e.employee_id) counter

from employees e,departments d

where d.department_name='Sales';

select * from v_hsd_006;

create or replace view v_hsd_007 as

select avg(e.salary) avg_salary

from employees e,departments d

where e.department_id=d.department_id and d.department_name='Sales';

SELECT AVG(E.SALARY) AS AVERAGE

FROM EMPLOYEES E

INNER JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID

WHERE D.DEPARTMENT_NAME LIKE '%Sales%'

create or replace view v_hsd_008 as

select e.department_id,d.department_name,avg(e.salary) avg_salary

from employees e,departments d

where e.department_id=d.department_id

group by e.department_id,d.department_name

having avg(e.salary)&gt10000;

SELECT DEP.DEPARTMENT_ID,DEP.DEPARTMENT_NAME,AVG(EMP.SALARY) avg_SALARY

FROM DEPARTMENTS DEP,EMPLOYEES EMP

WHERE EMP.DEPARTMENT_ID = DEP.DEPARTMENT_ID

GROUP BY DEP.DEPARTMENT_ID,DEP.DEPARTMENT_NAME

HAVING AVG(EMP.SALARY) &gt '10000'

select * from v_hsd_008;

create or replace view v_hsd_009 as

select e.employee_id,e.last_name,e.first_name,e.salary

from employees e,(select d.department_id id,max(x.salary) max_salary

from employees x,departments d