oracle查询语句（三）

子查询
–查询工资比scott高的所有员工信息
–1、查询scoot员工的工资，2、查询比scott工资高的员工信息。

select * from emp
where sal >(select sal from emp
where ename = 'SCOTT'）

–子查询所要解决的问题: 问题不能一步求解。
–注意的问题：
–1、将子查询放入括号中
– 2、采用合理的书写风格
–3、可以在主查询的where select from having 后面，放置子查询
–4、不可以在group by 后面放置子查询
–5、强调from后面放置的子查询
–6、主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用，即可。
–7、一般不再子查询中使用order by，但是在Top-N分析问题中，必须使用order by
–8、一般先执行子查询，在执行主查询；但相关子查询除外。（名词：相关子查询）
–9、单行子查询只能使用单行操作符，多行子查询只能使用多行操作符
–10、注意子查询中null
注意事项解释
–3、在主查询的where select from having中放置子查询

select ename,sal,(select job from emp where empno=7839) myjob
from emp;

–5.强调from后面放置子查询
–查询员工姓名和薪水

 select *
 from (select ename,sal
 from emp);

–主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用，即可。
–查询部门SALES部门的员工信息

select *
  from emp
  where emp.deptno = (select deptno
                from dept
            where dname = 'SALES')

–多行操作符
–in: 在集合中
–查询部门名称在SALES和ACCOUNTING的员工信息

 select *
 from emp
 where deptno in (select deptno
                from dept
                where dname = 'SALES' or
                dname = 'ACCOUNTING');

–any 和集合的任意一个值比较
–查询工资比30号部门任意一个员工高的员工信息。

 select *
  from emp
  where sal > any (select sal from emp where deptno = 30);

–类似于大于30号部门员工工资的最小值。可以改写成单行子查询

 select *
   from emp
   where sal > (select min(sal) from emp where deptno = 30);

–all 和集合的所有值比较
–查询工资比30号部门所有员工高的员工信息

select *
from emp
where sal > all (select sal
                 from emp
                where deptno=30);

–null值在多行子查询中的问题
–1、not in 等同于all，在多行子查询中返回的结果如果有空值，则不能使用。
–2、in 等同于any，可以使用在多行子查询中返回的结果为空的情况。
–查询不是老板的员工信息（意思是查询普通员工的信息）

select * 
    from emp 
    where deptno not in (select mgr 
                        from emp 
                    where mgr is not null)

练习题
–练习题一：
–知识：rownum（伪列）
使用rownum需要注意的问题：
1、行号永远按照默认的顺序生成
2、行号只能使用< <= ,不能使用> >=
–查询员工表中工资最高的前三名

 select rownum,empno,ename,sal
 from (select * from emp
           order by sal desc)
 where rownum <=3

–找到员工表中薪水大于本部门平均薪水的员工。

select e.empno,e.ename,e.sal,b.avgsal
from emp e,(select deptno,avg(sal) avgsal
            from emp 
            group by deptno) b
where e.sal > b.avgsal and e.deptno = b.deptno;

–统计员工总人数，以及员工每年入职的人数。

select count(*)total, sum(decode(to_char(hiredate,'yyyy'),'1981',1,0))"1981",
                      sum(decode(to_char(hiredate,'yyyy'),'1980',1,0))"1980",
                      sum(decode(to_char(hiredate,'yyyy'),'1982',1,0))"1982",
                      sum(decode(to_char(hiredate,'yyyy'),'1987',1,0))"1987"
from emp;

集合查询
查询10号和20号部门的员工

1、select * from emp where deptno in (10,20);
2、select * from emp where deptno=10 or deptno=20
3、集合运算
 select * from emp where deptno=10
    union
  select * from emp where deptno=20;

–利用集合运算实现group by的增强
注意
1.参与运算的各个集合必须列数相同且类型一致
2.采用第一个集合的表头作为最后的表头
3.如果排序，必须在每个集合后使用相同的order by
4.使用括号改变执行顺序。

select deptno,job,sum(sal) from emp group by deptno,job
    union
    select deptno,to_char(null),sum(sal) from emp group by deptno
    union
select to_number(null),to_char(null),sum(sal)from emp