oracle中子查询怎么写,ORACLE中的子查询

“子查询”就是查询中嵌套着另一个查询，也即通过SELECT语句的嵌套使用形成子查询。当我们不知道特定的查询条件时,可以用子查询来为父查询提供查询条件以获得查询结果。

ORACLE中常见的子查询有下面几类：

A、单行子查询(Single-row subqueries)。

B、多行子查询(Multirow subqueries)。

C、内部视图型子查询(Inline views)。

D、多列子查询(Multiple-column subqueries)。

在我们继续详细讨论子查询之前，先看看写子查询的一些特别要注意的地方：

A、子查询必须放在括号内。

B、子查询也必须放在比较操作符号的右边。

C、子查询最多可以嵌套到255级。

子查询中不能使用ORDER BY子句，即ORDER BY必须位于查询的最外层。

一、单行子查询(Single-Row Subqueries)

单行子查询并不是最后输出的结果只能返回一行，而是指子查询只能返回一行。

有一条规则我们必须记住：

当我们用“等于”比较操作符把子查询和父查询嵌套在一起时，父查询期望从子查询那里只得到一行返回值。

例一：

SELECT ename,deptno,sal FROM EMP

WHERE deptno=(SELECT deptno FROM dept

WHERE loc=’New York’);

在这个例子中，如果子查询“SELECT deptno FROM dept WHERE loc=’New York’”只返回一行，则这个例子能正确运行，否则将出错。原因见前面规则。

其它一些表明是单行子查询的比较操作符有“>，>=，”。

二、多行子查询(Multirow Subqueries)

多行子查询，意味着子查询返回的结果子集可以是多行。因此，我们通常用集合比较操作符(如：IN, NOT IN)把父查询和子查询连接起来。

例二：

SELECT ename,job,sal FROM EMP

WHERE deptno in ( SELECT deptno FROM dept

WHERE dname LIKE ‘A%’);

三、多列子查询(Multiple-Column Subqueries)：

前面两个例子中，主查询的WHERE子句中都只有一列。然而，有时需要主查询处理多列。例如，为了得到每个部门工资最高的员工信息，可以使用下面的例子。

例三：

SELECT deptno,ename,job,sal FROM EMP

WHERE (deptno,sal) IN (SELECT deptno,MAX(sal) FROM EMP

GROUP BY deptno);

(有关本节演示请参见实验演示)

在使用多列子查询时必须注意：

A、 主查询中，必须把WHERE子句中需要的多个列用括号括起来，否则发生错误。

B、 主查询WHERE子句中的列与子查询中返回的列必须匹配。

四、内联视图子查询(inline view Subqueries)：

通常的查询中，FROM子句后面都是具体的表名。然而，我们也可以在FROM后面跟上一个子查询作为中间

数据结果集，这个中间结果集就是内联视图(inline view)。带有内联视图的子查询就是内联视图子查

询。

例四：

SELECT ename,job,sal,rownum

FROM (SELECT ename,job,sal FROM EMP ORDER BY sal);

从这个例子我们应该注意到：

FROM子句后面的内联视图是可以使用ORDER BY子句进行排序的。然而，其它视图或子查询是不能用ORDER

BY进行排序的。

另外，我们在内联视图可以排序的基础上，可以使用“TOP-N”查询得到一些有意义的结果集。例如，可

以用下面的例子在EMP表中找出工资最低的五个员工的信息。

例五：

SELECT ename,job,sal,rownum

FROM ( SELECT ename,job,sal FROM EMP ORDER BY sal)

WHERE rownum<=5;

Rownum是表内部的一个指示行号的列。

五、到此，我们已经了解了四类子查询，但在使用子查询时还要注意以下几点：    A、 在HAVING子句中也可以使用子查询。例：SELECT deptno,job,AVG(sal) FROM EMPGROUP BY deptno,jobHAVING AVG(sal)>(SELECT sal FROM EMP WHERE ename=’MARTIN’);(有关本节演示请参见实验演示)B、 在一些比较复杂的子查询中，可能需要多次处理同一个子查询，为了优化这一步骤，ORACLE9i引入了“WITH”子句来提高这类子查询的性能。例：    SELECT dname,SUM(sal) AS dept_total    FROM EMP,DEPT    WHERE EMP.deptno=DEPT.deptno    GROUP BY dname    HAVING SUM(sal)>(SELECT SUM(sal)*1/3                      FROM EMP,DEPT                      WHERE EMP.deptno=DEPT.deptno)    ORDER BY SUM(sal) DESC;显然，子查询“SELECT SUM(sal) FROM EMP,DEPT WHERE EMP.deptno=DEPT.deptno”处理了两次。因此，可以利用O9i中的下来语法来改善性能：    WITH summary AS    ( SELECT dname,SUM(sal) AS dept_totalFROM EMP,DEPT      WHERE EMP.deptno=DEPT.deptno      GROUP BY dname )    SELECT dname,dept_total FROM summary    WHERE dept_total>(SELECT SUM(dept_total)*1/3 FROM summary)    ORDER BY dept_total DESC;   C、在一些子查询操作，诸如数据分组，可能会产生空值。然而，在子查询把这些数据集返回给主查询时又会忽略空值。因此，应特别小心这种情况。