|
时间的函数,包括高级的时间类型 1一般的时间函数: MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')--两个日期之间的月数,返回一个浮点数 ADD_MONTHS ('11-JAN-94',6)--添加月数 NEXT_DAY ('01-SEP-95','FRIDAY') --下一个星期五的日期 LAST_DAY('01-FEB-95')--当月的最后一天! ROUND(SYSDATE,'MONTH') --四舍五入月 ROUND(SYSDATE ,'YEAR') --四舍五入年 TRUNC(SYSDATE ,'MONTH') --阶段月 TRUNC(SYSDATE ,'YEAR') --截断年 2从时间中提取年,月,日:使用函数extract select extract(year from sysdate) year,extract(month from sysdate), extract(day from sysdate) from dual; 3使用函数得到数月之后的日期:to_yminterval(‘01-02’)表示加上1年2月,不能够到天!! select hire_date,hire_date +to_yminterval('01-02') as hire_date_new from employees where department_id=20 得到多少天之后的日期:直接日期加数字! select hire_date +3 from employees where department_id=20 关于子查询和其他的编程思路 1group语句:和高级的应用语句: SELECT department_id, job_id, SUM(salary), COUNT(employee_id) FROM employees GROUP BY department_id, job_id ; 可以使用having进行约束: I.group by rollup:对n列组合得到n+1种情况 SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY ROLLUP(department_id, job_id); II.group by cube:得到2的n次方种情况 SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY CUBE (department_id, job_id) ; III.使用grouping得到一行中构成列的情况,只是返回1和0:是空的话就返回1,否则返回0(注意不要弄反了!) SELECT department_id DEPTID, job_id JOB, SUM(salary), GROUPING(department_id) GRP_DEPT, GROUPING(job_id) GRP_JOB FROM employees WHERE department_id < 50 GROUP BY ROLLUP(department_id, job_id); IV.grouping sets:根据需要得到制定的组合情况 SELECT department_id, job_id, manager_id,avg(salary) FROM employees GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id)); 2from中使用子查询:返回每个部门中大于改部门平均工资的与员工信息 SELECT a.last_name, a.salary, a.department_id, b.salavg FROM employees a,--下面的地方就是子查询了,主要返回的是一组数据! (SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) b WHERE a.department_id = b.department_id AND a.salary > b.salavg; 3exists语句的使用: SELECT employee_id, last_name, job_id, department_id FROM employees outer--下面的 exists里面的select选择出来的是随便的一个字符或者数字都可以 WHERE EXISTS ( SELECT 'X' FROM employees WHERE manager_id = outer.employee_id); 4厉害的with语句: WITH dept_costs AS (--定义了一个临时的表 SELECT d.department_name, SUM(e.salary) AS dept_total--其间定义了一个临时的列dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name),/*注意这里有逗号*/ avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) AS dept_avg FROM dept_costs)--这里的第二张临时表里面就引用了前面定义的临时表和之间的列! SELECT * FROM dept_costs WHERE dept_total > (SELECT dept_avg FROM avg_cost) ORDER BY department_name;---最后的查询语句中使用了前面的临时表 5遍历树: SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR manager_id = employee_id ;--自底向上的遍历树。 6top-n分析:找到工资最高的5个人。(行内视图) select rownum,employee_id from (select employee_id,salary from employees order by salary desc) where rownum<5; |
|