oracle SQL语句之子查询

在一个查询语句包含另外一个查询语句
可以写在from后面当一个新表使用
可以写在where子句中，作为条件
可以写在update语句中
可以写delete语句中的where子句中
--子查询的分类
单行子查询：返回最多一行数据。 运算符 =,!=,>,>=,<,<=
多行子查询：返回最少一行  运算符：any,all (any,all 与=,!=,>,>=,<,<=  一起使用），in
--按列
单列子查询

多列子查询

-------------------单行子查询
--查询工资最高的员工信息(编号，姓名，职位，工资）
select empno,ename,job,sal
from emp
where sal =(select max(sal) from emp);
--查询高于平均工资的员工信息((编号，姓名，职位，工资)
select empno,ename,job,sal
from emp
where sal>(select avg(sal) from emp);

--------------------多行子查询
--查询工资高于 7788,7369,7900中任意一个员工工资的信息
select empno,ename,sal
from emp
where sal >any(select sal from emp
where empno in(7788,7369,7900))   --950 3000 800 >最小值
order by sal;
--查询领导的信息（编号，姓名，职位，工资)
select  empno,ename,sal
from emp
where empno in(select distinct mgr from emp);
--查询不是领导的信息
select  empno,ename,sal
from emp
where empno not in(select distinct mgr from emp where mgr is not null);
--多列子查询(子查询结果的列的个数为多个）
--查询与'SMITH'同一部门并且同一职位的员工信息
select empno ,ename,job,deptno
from emp
where deptno = (select deptno from emp where ename='SMITH')
and job =(select job from emp where ename='SMITH');

select empno ,ename,job,deptno
from emp
where (deptno,job)=(select deptno,job from emp where ename='SMITH');
--查询每个部门中最高工资的员工信息
select empno,ename ,sal,deptno
from emp
where (deptno,sal)in
(select deptno,max(sal)
from emp
group by deptno);

--查询10号部门 最高工资的员工信息
select empno,ename,sal
from emp
where deptno=10 and sal=(select max(sal) from emp where deptno=10);

--分页查询
/*
1、总页数：19
    select ceil(count(rowid)/pagecount) from emp;
    
2、当前页码 :pageindex
3、显示的数据的个数:  pagecount;  3
select *
from (
select rownum rn,empno,ename,job,sal from
(select empno,ename,job,sal from emp order by sal))
where rn>(pageindex-1)*pagecount and rn<=pageIndex*pagecount;
或：
select *
from (
select rownum rn,empno,ename,job,sal from
(select empno,ename,job,sal from emp order by sal) where rownum<pageindex*pagecount)
where rn>(pageindex-1)*pagecount ;

rownum:行号
*/
--按员工工资排序升序
--第一页的数据
select empno,ename,job,sal
from emp
where rownum<=3
order by sal;
----------
select *
from (
select rownum rn,empno,ename,job,sal from
(select empno,ename,job,sal from emp order by sal))
where rn>(pageindex-1)*pagecount and rn<=pageIndex*pagecount;
--第二页
select *
from (
select rownum rn,empno,ename,job,sal from
(select empno,ename,job,sal from emp order by sal))
where rn>3 and rn<=6;
--第三页
select *
from (
select rownum rn,empno,ename,job,sal from
(select empno,ename,job,sal from emp order by sal))
where rn>6 and rn<=9;

----------------------------
--要求，查询10号部门的员工信息（分页显示，每页显示两条数据，并按工资升序显示）
select * from(select rownum rn,empno,ename,job,sal from (
select empno,ename,job,sal from emp where deptno=10 order by sal))
where rn>0 and rn<=2;
--
select * from(select rownum rn,empno,ename,job,sal from (
select empno,ename,job,sal from emp where deptno=10 order by sal))
where rn>2 and rn<=4;

select e.empno,e.ename,e.job,e.sal,e.hiredate,d.dname
from emp e inner join dept d using(deptno)
where ename like '%A%';