子查询
在select语句中嵌套了另一个select语句
1)where 子句中嵌套子查询
2)用子查询的结果 作为字段来出现
--1)where 子句中嵌套子查询,执行顺序是
先执行子查询 再执行主查询
找出工资高于公司平均工资的所有员工??
select * from emp where sal+nvl(comm,0) > (select avg(sal+nvl(comm,0)) from emp);
高于部门30中员工最高工资的其他员工???
select * from emp where sal+nvl(comm,0) > all (select sal+nvl(comm,0) from emp
where deptno = 30);
低于部门30中员工工资的其他员工???
select * from emp where sal+nvl(comm,0) < all (select sal+nvl(comm,0) from emp
where deptno = 30);
select * from emp where sal+nvl(comm,0) < any (select sal+nvl(comm,0) from emp
where deptno = 30);
--2)用子查询的结果 作为字段来出现
先执行主查询 再执行子查询
<1>找员工姓名和直接上级的名字
select ename as 员工姓名,(select ename from emp where empno = a.mgr) as 经理姓名
from emp a;
<2>显示部门名称和人数
select dname,(select count(*) from emp where deptno=a.deptno) as rs from dept a;
<3>显示每个部门的最高工资的员工
select * from emp a where (deptno, sal) in ( select deptno,max(sal) from emp group by deptno);
select a.* from emp a,( select deptno,max(sal) as msal from emp group by deptno) c where a.deptno = c.deptno and
a.sal = c.msal;
--最大值和最小值的比较 转化为人数的比较
select * from emp a where (select count(*) from
emp where deptno = a.deptno and
sal > a.sal) = 0 and a.deptno is not null;
<4>显示每个部门的工资前2名的员工
select * from emp a where (select count(*) from
emp where deptno = a.deptno and
sal > a.sal) <=1 and a.deptno is not null;
<6> 层次查询
--level 伪列 层次查询的时候可以使用的 层的编号
select lpad('+',level,' ')||ename from emp
connect by prior empno = mgr --父子关系 父结点中的empno = 子节点中的mgr
start with mgr is null;--从 mgr is null的节点 开始遍历
select lpad('+',level,' ')||ename from emp
connect by prior empno = mgr
start with ename = 'BLAKE';
<7> TOP 前几行 (sqlserver)
rownum (oracle伪列)
--emp表的前2行
--rownum必须使用<=的关系比较运算符
select * from emp where rownum <= 2;
select top 2 * from emp; --sqlserver的写法
select * from emp where rownum = 1;
--公司工资最高的2个人
/*select * from emp
where rownum <= 2
order by sal desc;*/ 错误的
select * from (select * from emp order by sal desc)
where rownum <= 2;
--分页查询
--每页显示4行 一共有14行记录
第1页 第1-4行
第2页 第5-8行
第3页 第9-12行
第4页 第13-14行
--希望每次页面显示的时候 都只查询回需要的记录
select * from (select rownum as num,emp.* from emp)
where num >= 9 and num <= 12;
select * from (select rownum as num,emp.* from emp)
where num >= 13 and num <= 14;