-----------------------------------------高级查询
--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 ;
oracle 高级查询及相关示例
最新推荐文章于 2024-06-03 10:26:43 发布