子查询
思考:
查询工资比JONES工资高的员工信息
分步
1)查询出JONES工资(查询员工信息为JONES的员工的工资)
SELECT sal
FROM emp
WHERE ename='JONES';
2)查询比上述工资高的员工信息
查询工资高于2975的员工信息
SELECT *
FROM emp
WHERE sal>1975;
SELECT *
FROM emp
WHERE sal>
(SELECT sal
FROM emp
WHERE ename='JONES');
子查询种类:根据查询结果来分类
单行
(只有一个结果,单行单列)<,> ,=,<=,>=,<>,!=
显示和雇员7369从事相同通知并且工资大于雇员7876的雇员的姓名和工作
分步
1)雇员7369从事的工作
SELECT job
FROM emp
WHERE empno=7369;
2)雇员7876的工资
SELECT sal
FROM emp
WHERE empno=7876;
3)查询job=clerk sal>1100
SELECT ename,job
FROM emp
WHERE job='clerk' AND salg>1100;
SELECT ename,job
FROM emp
WHERE job=
(SELECT job
FROM emp
WHERE empno=7369)
AND sal>(
SELECT sal
FROM emp
WHERE empno=7876);
查询工资最低的员工姓名,岗位及工资
SELECT ename,job,sal
FROM emp
having sal=min(sal);
1)最低工资
SELECT min(sal)
FROM emp;
2)sal=min()
SELECT ename,job,sal
FROM emp
WHERE sal=min()
SELECT ename,job,sal
FROM emp
WHERE sal=
(SELECT min(sal)
FROM emp);
查询部门最低工资比20号部门最低工资高的部门编号及最低工资
1)各部门的编号和最低工资
SELECT deptno,min(sal)
FROM emp
GROUP BY deptno;
2)20号部门的最低工资
SELECT min(sal)
FROM emp
WHERE deptno=20;
3)比对
SELECT deptno,min(sal)
FROM emp
GROUP BY deptno
HAVING min(sal)>
(SELECT min(sal)
FROM emp
WHERE deptno=20);
HAVING子句中用子查询
WHERE 或者 HAVING
条件里比较运算符左侧不含聚合函数的用 where
条件里比较运算符左侧含聚合函数的用 having
查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名,工资,部门名称
SELECT e.ename,e.sal,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno AND e.sal>
(SELECT sal
FROM emp
WHERE ename='SMITH')
AND d.loc='CHICAGO';
查询入职日期比20部门入职日期最早的员工还要早的员工姓名,入职日期
SELECT ename,hiredate
FROM emp
WHERE hiredate<
(SELECT min(hiredate)
FROM emp
WHERE deptno=20);
查询部门人数大于所有部门平均人数的部门编号,部门名称,部门人数
SELECT d.deptno,d.dname,count(*)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY deptno
HAVING count(e.empno)>
(SELECT count(e.empno)/count(DISTINCT d.deptno)
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno);
FROM 中使用子查询(了解 经常可以用视图来代替)
查询比自己部门平均工资高的员工姓名,工资,部门编号,部门平均工资
1)如果有一张表记录着部门编号,该部门的平均工资可以解决
SELECT deptno,avg(sal) avgsal
FROM emp
GROUP BY deptno;
SELECT e.ename,e.sal,e.deptno,a.avgsal
FROM emp e,
(SELECT deptno,avg(sal) avgsal
FROM emp
GROUP BY deptno) a
WHERE e.deptno=a.deptno AND sal>a.avgsal;
SELECT e.ename,e.sal,e.deptno,a.avgsal
FROM emp e,
(v_dept_avgsal) a
WHERE e.deptno=a.deptno AND sal>a.avgsal;
视图 v_dept_avgsal :
SELECT deptno,avg(sal) avgsalFROM emp GROUP BY deptno
2)多行子查询:
in
查询是经理的员工姓名,工资
SELECT mgr FROM emp WHERE mgr IS NOT NULL;
SELECT ename,sal
FROM emp
WHERE empno IN(SELECT mgr FROM emp WHERE mgr IS NOT NULL);
子查询中的空值
查询不是经理的员工姓名
SELECT ename,sal
FROM emp
WHERE empno NOT IN(SELECT mgr FROM emp WHERE mgr IS NOT NULL);
ANY 任意
=ANY 相当于 in
查询比10号部门任意一个人的工资高的员工信息
(比最小的大即可)
SELECT *
FROM emp
WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=10);
ALL 所有
查询比10号部门所有一个人的工资高的员工信息
(比最大的大即可)
SELECT *
FROM emp
WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=10);
查询部门编号不为20,且工资比20部门所有员工工资高的员工编号,姓名,职位,工资。
SELECT deptno,ename,job,sal
FROM emp
WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=20) AND deptno<>20;
查询部门编号不为10,且工资和10部门所有员工工资相等的员工编号,姓名,职位,工资。
SELECT deptno,ename,job,sal
FROM emp
WHERE sal=ALL(SELECT sal FROM emp WHERE deptno=10) AND deptno<>10;
查询部门编号不为20,且工资比20部门任意一个员工工资高的员工编号,姓名,职位,工资。
SELECT deptno,ename,job,sal
FROM emp
WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=10) AND deptno<>20;
查询部门编号不为10,且工资和10部门任意一个员工工资相等的员工编号,姓名,职位,工资
SELECT deptno,ename,job,sal
FROM emp
WHERE sal=ANY(SELECT sal FROM emp WHERE deptno=10) AND deptno<>10;
3)多列 ()