sql 子查询

1. 多列子查询
select deptno, ename, job, sal from EMP
Where(deptno, sal) IN (select deptno, max(sal) from EMP GROUP by deptno)


2.多行子查询:
SELECT ename, job, sal FROM EMP WHERE deptno in (SELECT deptno FROM dept WHERE dname LIKE 'A%');

3. 内联视图子查询:
select ename, job, sal,rownum FROM (SELECT ename, job, sal FROM EMP ORDER BY sal);
select ename, job, sal, rownum FROM (SELECT ename, job, sal FROM EMP ORDER BY sal);

4. 在HAVING字句中使用子查询
SELECT deptno, job, AVG(sal) FROM EMP GROUP BY deptno, job HAVING AVG(sal)> (SELECT sal FROM EMP WHERE ename = 'MARTIN');