oracle 高级查询及相关示例

-----------------------------------------高级查询
 --1,随机返回5条记录dbms_random.value()
 select * from (select ename,job from emp order by dbms_random.value()) where rownum<=5
 --2,处理空值排序
 select * from emp order by comm desc nulls last/last;
 --3,查询跳过表中的偶数行
 select ename,x.rn from (select row_number() over(order by ename) rn,ename from emp) x where mod(rn,2)=1;
 select ename from (select row_number() over (order))
 --4,查询员工信息与其中工资最高最低员工
 select ename,sal,max(sal) over(),min(sal) over() from emp;
 --5,连续求和
 select ename,sal,sum(sal) over(),sum(sal) over(order by ename) from emp;
 --6分部门求和
 select emp.empno,ename,sal,b.deptno,b.a from emp join
 ( select deptno,empno,sum(sal) over(partition by deptno order by empno desc) a from emp)b on emp.empno = b.empno  ;