plsql的子查询

1，什么是子查询

    子查询在包含它的SQL语句执行前被执行，子查询产生的结果在包含它的SQL语句执行完成后就被丢弃了。所以子查询常被认为是包含它的语句范围内的临时表。

关联、非关联子查询

      关联子查询中引用了一列或多列包含它的SQL中的字段，否则是非关联子查询。

 

2，非关联子查询

子查询按结果分为下面三种情况

      单行单列子查询、多行单列子查询和多列子查询。

单行单列子查询(也叫标量查询，因为它的结果用作父查询的标量)

      可以包含比较操作符。

      子查询和父查询都可以引用相同的表。如下

          DELETE FROM load_log  

               WHERE load_dt < (SELECTMAX(TRUNC(load_dt))  FROM load_log);

      非关联子查询也普遍存在于WHERE之外。如下

               SELECT sales_emp_id, COUNT(*)

                    FROM cust_order

                    GROUP BY sales_emp_id

                    HAVING COUNT(*) = (SELECT MAX(COUNT(*))

                                  FROM cust_order

                                  GROUP BY sales_emp_id);

    上面的语句中，子查询和父查询使用了相同的合计函数。这种查询类型看起来很浪费资源。（以后会介绍更有效的方法来描述这样的查询）

非关联查询的使用情况

     FROM语句可以包含任意类型的非关联子查询。

     SELECT和ORDER BY可以包含标量子查询。

     GROUP BY语句不能包含子查询。

     SHART WITH和CONNECT BY语句可以包含子查询。

     WITH语句包含一个命名的非关联子查询，它可以被父查询引用多次而只执行一次。

     MERGE的USING语句可以包含非关联子查询。

     UPDATE的SET语句可以包含标量子查询或者单行多列子查询

     INSERT语句可以在VALUES的位置包含标量子查询。

多行单列子查询

       使用ANY和ALL比较符进行比较。因为这类查询的结果是多行，而普通的比较操作符不能将父查询中单一的值和子查询的结果集进行比较，但是却可以和结果集中的每个值进行比较，所以ORACLE使用了ANY（any member of the set）和ALL（all members of the set）操作符。      

               SELECT fname, lname

               FROM employee

               WHERE dept_id = 30 AND salary >= ALL

                          (SELECT salary

                                FROM employee

                                WHERE dept_id = 30);（推荐）

              或

               SELECT fname, lname

               FROM employee

               WHERE dept_id = 30 AND NOT salary < ANY

                          (SELECT salary

                                FROM employee

                                WHERE dept_id = 30);

    上面的SQL含义为查询部门中薪水最高的员工。

    经常一个查询可以用不同的查询方法来书写，比如上面两个实现了同样的功能，但是第一个由于可读性更好而被推荐，而事实上第二个语句的性能更优。我们在书写SQL的时候经常要在性能和可读性之间找到一个平衡点。

    在不确定子查询只返回一行的情况下，最好是使用ANY或ALL作为比较符。 IN同ANY,ALL一样也用于多行子查询，IN在功能上等同于= ANY,只要在子查询的结果集中找到一个箱匹配的则返回TRUE。NOT IN相当于!=ANY。

    NOT IN的含义是查找一个结果集的值，同时这些值在另一个结果集中并不存在，这也被称作反连接(anti-join)。

多列子查询

    SET之后，如下

               UPDATE monthly_orders

               SET (tot_orders, max_order_amt, min_order_amt, tot_amt) =

                      (SELECT COUNT(*), MAX(sale_price), MIN(sale_price), SUM(sale_price)

                            FROM cust_order

                            WHERE order_dt >= TO_DATE('01-JUL-2001','DD-MON-YYYY')

                                   AND order_dt < TO_DATE('01-AUG-2001','DD-MON-YYYY')

                                   AND cancelled_dt IS NULL)

               WHERE month = 7 and year = 2001;

    WHERE之后，如下

               DELETE FROM line_item

               WHERE (order_nbr, part_nbr) IN

                     (SELECT c.order_nbr, p.part_nbr

                           FROM cust_order c INNER JOIN line_item li

                           ON c.order_nbr = li.order_nbr

                           INNER JOIN part p

                           ON li.part_nbr = p.part_nbr

                           WHERE c.ship_dt IS NULL AND c.cancelled_dt IS NULL

                                       AND p.status = 'DISCONTINUED');

WITH语句

    如果同一个非关联子查询在同一次查询中被使用多次，这种情况可以使用ORACLE9I提供的WITH语句，WITH语句创建了命名的一个临时的数据集。这个只产生一次数据集可以在整个查询中使用多次，使用这个数据集就和使用表一样。

               WITH avg_sal AS (SELECT AVG(salary) val FROM employee)

               SELECT e.emp_id, e.lname, e.fname,

                           (SELECT ROUND(e.salary - val) FROM avg_sal) above_avg

               FROM employee e

               WHERE e.salary > (SELECT val FROM avg_sal);

(未完)

 

转载于:https://www.cnblogs.com/wzc998/archive/2008/08/31/1280797.html