第八章 高级查询

?

随即返回5条记录

Select * from (select ename, job from emp order by dbms_random.value() ) where rownum <=5;

?

处理空值排序

Select * from emp order by comm. Desc nulls last(first);? --默认为first

?

查询跳过偶数行

Select ename from (select row_number() over (order by ename) rn, ename from emp) x where mod(rn,2) = 1;

?

连续求和

Select ename,sal, sum(sal) over(), sum(sal) over(order by ename) from emp;

Sum(sal) over(order by ename)指的是连续求和，是以ename排序。若有两个这样的窗口函数，以后面的为主。

?

分部门连续求和

Select deptno, ename, sal, sum(sal) over(partition by deptno order by ename) as s from emp;

?

?

得到当前行上一行或者下一行的数据

Select ename,sal, lag(sal) over (order by sal) before, lead(sal) over(order by sal) last from emp;

要求用一条SQL语句，统计每个月及上个月和下个月的总收入

Select month, sum(income),lag(sum(income)) over(order by sum(income)) as 上月收入,lead(sum(income)) over(order by sum(income)) as 下月收入 from table group by month;

?

Select add_months(trunc(sysdate, ‘y’), 12) – trunc(sysdate,’y’) from dual;

?

第九章 数据字典

?

查询某用户下的所有表

Select table_name from all_tables where owner=’SCOTT’;

查询表的所有字段

Select * from all_tab_columns where table_name=’EMP’;

查询表的索引列

Select * from sys.all_ind_columns where table_name=’EMP’;<