10、高级查询

随机返回 5 条记录

Select * from (select ename,job from emporder by dbms_random.value()) where rownum<=5；

---

处理空值排序

select * from emp order by comm descnulls last(first);

 

---

查询跳过表中的偶数行

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

 

---

查询员工信息与其中工资最高最低员工

select ename,sal,max(sal) over(),min(sal) over() from emp;

 

---

连续求和

select ename,sal,sum(sal) over(),

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

 

---

 

分部门连续求和

select deptno,sal ,sum(sal) over

(partition by deptno order by ename) as s from emp;分部门连续求和 

 

sum(sal) over (partition by deptno) 分部门求和

 

---

 

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

select ename,sal,lead(sal) over(order bysal) aaa ,lag(sal) over(order by sal) bbb from emp;