一、单行子查询:= < > >= <= <>
- SELECTename
FROM emp
WHERE sal>(SELECT sal
FROM emp
WHERE ename='JONES');
- SELECTename,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
WHERE sal=(SELECT MIN(sal)
FROM emp);
- HAVING子句中的子查询
SELECT deptno,MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal)>(SELECT MIN(sal)
FROM emp
WHERE 57 ptno=20);
查询哪个部门的员工人数高于平均人数
SELECT deptno,count(empno)
FROM emp
GROUP BY deptno
HAVING COUNT(empno)>(
SELECT AVG(COUNT(empno))
FROM emp
GROUP BY deptno);
二、多行子查询 IN / ANY / ALL
IN
SELECT ename,sal
FROM emp
WHERE empno IN(SELECT mgr FROM emp);
ANY(或)
(1) >ANY (2)
查询部门编号不为10,工资比10部门任意一名员工工资高的 编号、姓名.....
SELECT empno,ename,job,sal
FROM emp
WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=10)
AND deptno<>10;
ALL(与)
查询部门编号不为10,工资比10部门所有员工工资高的 编号、姓名.....
SELECT empno,ename,job,sal
FROM emp
WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=10)
AND deptno<>10;
三、多列子查询
- SELECTename,deptno,job,hiredate
FROM emp
WHERE (deptno,job) IN
(SELECT deptno,job FROM emp WHERE
TO_CHAR(hiredate,'yyyy')='1981')
AND TO_CHAR(hiredate,'yyyy')<>'1981';
- 查询和1981年入职的任意一个员工的部门或职位相同员工姓名、部门、职位、入职日期,不包括1981入职员工。
SELECT ename,deptno,job,hiredate
FROM emp
WHERE deptno IN
(SELECT deptno FROM emp WHERE TO_CHAR(hiredate,'YYYY')='1981')
AND job IN
(SELECT job FROM emp WHERE TO_CHAR(hiredate,'YYYY')='1981')
AND TO_CHAR(hiredate,'YYYY')<>'1981';
四、在FROM子句中使用子查询
SELECT a.ename,a.sal,a.deptno,b.salavg
FROM emp a,
(SELECT deptno,avg(sal) salavg FROM emp GROUP BY deptno) b
WHERE a.deptno=b.deptno
AND a.sal>b.salavg
五、分页查询
1、ROWNUM介绍(就是方便查看的序号,实现分页)
SELECT ROWNUM,ename,job,mgr from emp;
2、TOP-N查询
//就输出指定条记录
SELECT * FROM emp WHERE ROWNUM<=5;
3、ROWNUM分页
SELECT ename,hiredate
FROM emp
WHERE ROWNUM<=5
ORDER BY hiredate ASC;
SELECT emp.ename,emp.hiredate
FROM emp,dept
WHERE ROWNUM<=2 AND dept.loc='CHICAGO'
ORDER BY hiredate ASC;
SELECT * FROM
(SELECT a.*,ROWNUM nu FROM
(SELECT * FROM emp) a
WHERE ROWNUM<=6)
WHERE nu>3;