select deptno,ename, sal
from emp
where sal in (select max(sal) from emp group by deptno) or
sal in (select max(sal)
from (select sal,deptno
from emp where sal not in
(select max(sal) from emp group by deptno)) group by deptno)
order by 1;
select ename,deptno,sal from emp e where (select count(*) from emp where sal>e.sal and deptno=e.deptno)<2;
select count(*) from emp where sal>800 and deptno=20;
select * from (select ename,deptno,sal,rank () over (partition by deptno order by sal desc) Ord from emp) where ord<=2;
select ename,deptno,sal,row_number () over (partition by deptno order by sal desc) Ord from emp;
select * from (select rownum rn,a.* from (select ename,sal from emp order by sal desc) a) where rn between 6 and 10;
select * from (select * from emp order by dbms_random.value()) where rownum<=3;
查询雇员的姓名，工资，税，(1级不缴税，2-->2% ,3-->3%,4-->4%,5-->5%)
select
e.ename,
e.sal,
(sal*decode(s.grade,1,0,2,0.02,3,0.03,4,0.04,5,0.05,0)) tax
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
部门总工资和部门上缴个税总和
select deptno,sum(sal),sum(tax)
from
(select
e.sal,
(sal*decode(s.grade,1,0,2,0.02,3,0.03,4,0.04,5,0.05,0)) tax,
deptno
from emp e,salgrade s
where e.sal between s.losal and s.hisal)
group by deptno;
比WARD奖金低的人？
select ename,comm from emp where NVL(comm,0)<(select comm from emp where ename='WARD');
select ename,comm from emp where comm<(select comm from emp where ename='WARD') or comm is null;
奖金最高的前两名雇员？
select * from (select ename,comm from emp order by comm desc nulls last) where rownum<=2;
select * from (select ename,comm from emp where comm is not null order by comm desc) where rownum<=2;
工资高于本部门平均工资的人？
使用替代变量进行分页查询
select * from (select rownum rn,a.* from (select * from emp order by sal desc) a)
where rn between &p*5-4 and &p*5;
|