一个菜鸟的oracle之路----------三(二)
//大于最大值
[sql]
select ename,salary
from emp_xxx
where salary>All(select salary from emp_xxx where ename='黄蓉');
//大于最大值，本表中存在2位叫黄蓉的同学
2.3 Any
案例12 //大于最小值
[sql]
select ename,salary
from emp_xxx
where salary>any(select salary from emp_xxx where ename='黄蓉');
2.4 In
案例13 谁和刘苍松同部门？
[sql]
select ename
from emp_xxx
where deptno=(select deptno from emp_xxx where ename='刘苍松');
谁和刘苍松同部门？ 列出除刘苍松之外的员工名字
用子查询实现同一功能
[sql]
select ename
from emp_xxx
where deptno=(select deptno from emp_xxx where ename='刘苍松')
and ename<>'刘苍松';
2.5 单行比较运算符和All Any in
案例14 谁和刘苍松同部门？列出除刘苍松之外的员工的名字
（如果子查询得到的结果十多个）
[sql]
insert into emp_xxx(empno,ename,deptno)values(1015,'刘苍松',20);
values(1015,'刘苍松',20)
//如果子查询得到的结果是多个，不能使用单行比较运算符，应该改为in
//否则会报错，ORA-01427 单行子查询返回多个行
[sql]
select ename,salary,job,deptno
from emp_xxx
where deptno in(select deptno from emp_xxx
where ename='刘苍松')
and ename<>'刘苍松';
案例15 谁是张无忌的下属，如果只有一个叫张无忌的员工，则无问题
如果有多个，需要用in
[sql]
select empno from emp_xxx
where ename='张无忌'
//select ename from emp_xxx
// where mgr in(1001,1014);
select ename
from emp_xxx
where mgr in(select empno from emp_xxx
where ename='张无忌');
小结：单行比较运算符和All Any in
返回一行 > < <= = <>
返回多行 >ALL >Any
2.6 子查询结果返回多列的情况
案例16 每个部门拿最高薪水的是谁？
//首先求出每个部门拿最高薪水的薪水是多少
[sql]
select deptno,max(salary)
from emp_xxx
where deptno is not null
group by deptno;
//然后求出该部门拿最高薪水的人是谁？
[sql]
select ename, salary,deptno
from emp_xxx
where (deptno,salary) in(select deptno,max(salary)
from emp_xxx
where deptno is not null
group by deptno);
//感觉该题还没有完全掌握，
//明白了，不明白请看案例14
2.7 子查询出现在having短语中
案例17 哪个部门的人数比部门30人多？
[sql]
select count(*)
from emp_xxx
where deptno=10;
//我的错误解法
[sql]
select deptno,count(*)
from emp_xxx
group by deptno
where count(*)>(select count(*)
from emp_xxx
where deptno=30);
//正确答案
[sql]
select deptno,count(*)
from emp_xxx
group by deptno
having count(*)>=(select count(*)
from emp_xxx
where deptno=10)
and deptno<>10;
//这个案例题很经典，学习到了group by having <>
案例18 哪个部门的平均薪水比部门20的平均薪水高？
[sql]
select avg(nvl(salary,0))
from emp_xxx
where deptno=20;
select deptno
from emp_xxx
group by deptno
having avg(nvl(salary,0))>(select avg(nvl(salary,0))