ORACLE 高级子查询

:: 写多列子查询:: 在返回空值时描述并解释子查询的行为:: 写一个在 FROM 子句中的子查询:: SQL 中使用分级子查询:: 描述能够用相关子查询解决的问题类型:: 写相关子查询:: 用相关子查询更新和删除行:: 使用 EXISTS 和 NOT EXISTS 操作:: 使用 WITH 子句Lesson Aim   In this lesson, you learn how to write multiple-column subqueries and subqueries in the FROM clause of a SELECT statement. You also learn how to solve problems by using scalar, correlated subqueries and the WITH clause.什么是子查询? 一个子查询是一个嵌入 SELECT 语句中的另一个 SQL 语句的子句select ...   主查询---->  from   ...where  ... (select ... from   ...      <-----子查询 where  ...)What Is a Subquery?  A subquery is a SELECT statement that is embedded in a clause of another SQL statement, called the parent statement.内查询把查询结果给外查询 The subquery (inner query) returns a value that is used by the parent statement. Using a nested subquery is equivalent to performing two sequential queries and using the result of the inner query as the search value in the outer query (main query).  Subqueries can be used for the following purposes:     :: To provide values for conditions in WHERE, HAVING, and START WITH clauses of SELECT statements    :: To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement/././././    :: To define the set of rows to be included in a view or snapshot in a CREATE VIEW or CREATE SNAPSHOT statement/./././同上    :: To define one or more values to be assigned to existing rows in an UPDATE statement////同上    :: To define a table to be operated on by a containing query. (You do this by placing the subquery in the FROM clause. This can be done in INSERT, UPDATE, and DELETE statements as well.)/,.,.FROM子查询  Note: A subquery is evaluated once for the entire parent statement.先执行内查询,返回值给外查询,再执行主查询.子查询SELECTselect_listFROMtableWHEREexpr operator (SELECT select_list         FROM  table);:: 子查询 (内嵌查询) 在主查询中执行一次:: 子查询的结果被用于主查询 (外查询)Subqueries  You can build powerful statements out of simple ones by using subqueries. Subqueries can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself or some other table. Subqueries are very useful for writing SQL statements that need values based on one or more unknown conditional values. In the syntax:././././.operatorincludes a comparison operator such as >, =, or IN  Note: Comparison operators fall into two classes: (比较运算)single-row operators (>, =, >=, <, <>, <=)multiple-row operators (IN, ANY, ALL).  The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The inner and outer queries can retrieve data from either the same table or different tables.(叫做内嵌select,子select,内部select)使用子查询SELECT last_nameFROM   employees         10500WHERE  salary >    <-------------|                (SELECT salary   |                 FROM   employees                 WHERE  employee_id = 149) ;Using a Subquery  //对上面的解释  In the example in the slide, the inner query returns the salary of the employee with employee number 149. The outer query uses the result of the inner query to display the names of all the employees who earn more than this amount.Example: Display the names of all employees who earn less than the average salary in the company.   SELECT last_name, job_id, salary   FROM   employees   WHERE  salary < (SELECT AVG(salary)                    FROM   employees);多列子查询  主查询的每行与一个多行多列子查询的值比较Main query <----------|WHERE(manager_id,department_id) IN<-------|   ||   | <-----||   |       ||   |Subquery       ||   |10090_______||   |10260________|   |12450____________|Multiple-Column Subqueries So far you have written single-row subqueries and multiple-row subqueries where only one column is returned by the inner SELECT statement and this is used to evaluate the expression in the parent select statement. If you want to compare two or more columns, you must write a compound WHERE clause using logical operators(如果想要比较两行或更多行你要使用逻辑运算符写一个混合的WHERE子句). Using multiple-column subqueries, you can combine duplicate WHERE conditions into a single WHERE clause.(使用混合列子查询可以把多个WHERE条件合到一个WHERE子句.)  SyntaxSELECT column,column, ...FROM tableWHERE (column,column, ...) IN   (SELECT column,column, ...    FROM table    WHERE condition);   The graphic in the slide illustrates that the values of the MANAGER_ID and DEPARTMENT_ID from the main query are being compared with the MANAGER_ID and DEPARTMENT_ID  values retrieved by the subquery. Since the number of columns that are being compared are more than one, the example qualifies as a multiple-column subquery.列比较在一个多列子查询中的列比较能够被：:: 成对地比较:: 非成对的比较 Pairwise versus Nonpairwise Comparisons成对,非成对比较      Column comparisons in a multiple-column subquery can be pairwise comparisons or nonpairwise comparisons. /././././.在select语句的每个条件行都要有相同的列.   In the example on the next slide, a pairwise comparison was executed in the WHERE clause. Each candidate row in the SELECT statement must have both the same MANAGER_ID column and the DEPARTMENT_ID as the employee with the EMPLOYEE_ID 178 or 174.      A multiple-column subquery can also be a nonpairwise comparison. In a nonpairwise comparison, each of the columns from the WHERE clause of the parent SELECT statement are individually compared to multiple values retrieved by the inner select statement. The individual columns can match any of the values retrieved by the inner select statement. But collectively, all the multiple conditions of the main SELECT statement must be satisfied for the row to be displayed. The example on the next page illustrates a nonpairwise comparison.成对比较子查询   显示雇员的细节，这些雇员被同一个经理管理，并且，工作在同一个部门，具有 EMPLOYEE_ID 178 或 174SELECTemployee_id, manager_id, department_idFROMemployeesWHERE  (manager_id, department_id) IN                      (SELECT manager_id, department_id                       FROM   employees                       WHERE  employee_id IN (178,174))ANDemployee_id NOT IN (178,174);SQL> select manager_id,department_id  2  from employees  3  where employee_id in (178,174);MANAGER_ID DEPARTMENT_ID---------- -------------       149            80       149SQL> SELECT     employee_id, manager_id, department_id  2  FROM       employees  3  WHERE  (manager_id, department_id) IN  4                        (SELECT manager_id, department_id  5                         FROM   employees  6                         WHERE  employee_id IN (178,174))  7  AND        employee_id NOT IN (178,174);EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID----------- ---------- -------------        179        149            80        177        149            80        176        149            80        175        149            80SQL> SELECT     employee_id, manager_id, department_id  2  FROM       employees  3  WHERE  (manager_id, department_id) IN  4                        (SELECT manager_id, department_id  5                         FROM   employees  6                         WHERE  employee_id IN (178,174))  7  ;EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID----------- ---------- -------------        179        149            80        177        149            80        176        149            80        175        149            80        174        149            80/./././.Pairwise Comparison Subquery//   The example in the slide is that of a multiple-column subquery because the subquery returns more than one column(子查询返回值多于一行). It compares the values in the MANAGER_ID column and the DEPARTMENT_ID column of each row in the EMPLOYEES table with the values in the MANAGER_ID column and the DEPARTMENT_ID column for the employees with the EMPLOYEE_ID 178 or 174.  First, the subquery to retrieve the MANAGER_ID and DEPARTMENT_ID values for the employees with the EMPLOYEE_ID 178 or 174 is executed. These values are compared with the MANAGER_ID column and the DEPARTMENT_ID column of each row in the EMPLOYEES table. If the values match, the row is displayed. In the output,  the records of the employees with the EMPLOYEE_ID 178 or 174 will not be displayed. The output of the query in the slide follows.非成对比较子查询    显示被同一个经理管理，具有 EMPLOYEE_ID 174 或 141 的雇员；并且，工作在同一个部门，具有 EMPLOYEE_ID 174 或 141 的雇员的详细信息SELECT  employee_id, manager_id, department_id 3/FROM    employeesWHERE   manager_id IN                   (SELECT  manager_id1/                   FROM    employees                   WHERE   employee_id IN (174,141))AND     department_id IN                   (SELECT  department_id2/                   FROM    employees                   WHERE   employee_id IN (174,141))ANDemployee_id NOT IN(174,141);返回的department_id值和manager_id值与departments表中的每一行进行比较.要两个值同时都满足才display.Nonpairwise Comparison Subquery   The example shows a nonpairwise comparison of the columns. It displays the EMPLOYEE_ID, MANAGER_ID, and DEPARTMENT_ID of any employee whose manager ID matches any of the manager IDs of employees whose employee IDs are either 174 or 141 and DEPARTMENT_ID match any of the department IDs of employees whose employee IDs are either 174 or 141.   First, the subquery to retrieve the MANAGER_ID values for the employees with the EMPLOYEE_ID 174 or 141 is executed. Similarly, the second subquery to retrieve the DEPARTMENT_ID values for the employees with the EMPLOYEE_ID 174 or 141 is executed. the retrived values of the MANAGER_ID and DEPARTMENT_ID columns are compared with the MANAGER_ID and DEPARTMENT_ID column for each row in the EMPLOYEES table. If the MANAGER_ID column of the row in the EMPLOYEES table matches with any of the values of the MANAGER_ID retrieved by the inner subquery and if the DEPARTMENT_ID column of the row in the EMPLOYEES table matches with any of the values of the DEPARTMENT_ID retrieved by the second subquery, the record is displayed. The output of the query in the slide follows.    EMPLOYEE_IDMANAGER_IDDEPARTMENT_ID    142       124 50    143       124 50    144       124 50    176       149 80在 FROM 子句中使用子查询SELECT  a.last_name, a.salary,         a.department_id, b.salavg//必须是b 表中的'列'FROM    employees a, (SELECT   department_id,                       AVG(salary) salavg                      FROM     employees                      GROUP BY department_id) bWHERE   a.department_id = b.department_idAND     a.salary > b.salavg;Using a Subquery in the FROM Clause    You can use a subquery in the FROM clause of a SELECT statement, which is very similar to how views are used. A subquery in the FROM clause of a SELECT statement is also called an inline view(内部视图). A subquery in the FROM clause of a SELECT statement defines a data source for that particular SELECT statement, and only that SELECT statement. The example on the slide displays employee last names, salaries, department numbers, and average salaries for all the employees who earn more than the average salary in their department. The subquery in the FROM clause is named b, and the outer query references the SALAVG column using this alias.//注意表的别名.分级子查询表达式:: 一个分级子查询表达式是一个从一行中返回确切的一个列值的子查询:: 在 Oracle8i 中，分级子查询仅在一些有限情况的情况下被支持，例如：     - SELECT 语句 (FROM 和 WHERE 子句)     - 在一个 INSERT 语句中的VALUES 表中:: 在 Oracle9i 中，分级子查询能够被用于：     - DECODE  and CASE 的条件和表达式部分     - 除 GROUP BY 以外所有的 SELECT 子句Scalar Subqueries in SQL   A subquery that returns exactly one column value from one row is also referred to as a scalar subquery(一个分级子查询表达式是一个从一行中返回确切的一个列值的子查询.) Multiple-column subqueries written to compare two or more columns, using a compound WHERE clause and logical operators, do not qualify as scalar subqueries.././././././如果子查询返回0行,分级子查询表达式是NULL,如果子查询返回多行,Oracle Server返回ERROR. If the subquery returns 0 rows, the value of the scalar subquery expression is NULL. If the subquery returns more than one row, the Oracle Server returns an error.  The value of the scalar subquery expression is the value of the select list item of the subquery. If the subquery returns 0 rows, the value of the scalar subquery expression is NULL. If the subquery returns more than one row, the Oracle Server returns an error. The Oracle Server has always supported the usage of a scalar subquery in a  SELECT statement. The usage of scalar subqueries has been enhanced in Oracle9i. You can now use scalar subqueries in:   - Condition and expression part of DECODE and CASE   - All clauses of SELECT except GROUP BY   - In the left-hand side of the operator in the SET clause and WHERE clause of UPDATE statement   However, scalar subqueries are not valid expressions in the following places:   - As default values for columns and hash expressions for clusters   - In the RETURNING clause of DML statements   - As the basis of a function-based index基本函数索引   - In GROUP BY clauses, CHECK constraints,  WHEN conditions///./././   - HAVING clauses./././   - In START WITH and CONNECT BY clauses   - In statements that are unrelated to queries, such as CREATE PROFILE分级子查询: 例子  在CASE表达式的分级子查询.SELECT employee_id, last_name,       (CASE20 <----|          WHEN department_id =|                (SELECT deaprtment_id FROM deaprtments                 WHERE location_id=1800)        THEN 'Canada' ELSE 'USA' END) locationFROM   employees;......EMPLOYEE_ID LAST_NAME                 LOCATI----------- ------------------------- ------        199 Grant                     USA        200 Whalen                    USA        201 Hartstein                 Canada        202 Fay                       Canada        203 Mavris                    USA        204 Baer                      USA...   在 ORDER BY 子查询中的分级子查询SELECT   employee_id, last_nameFROM     employees e//两个表ORDER BY(SELECT department_name//用deaprtments这个表的department_name排序 FROM departments d WHERE e.department_id = d.department_id);Scalar Subqueries: Examples  The first example in the slide demonstrates(认证) that scalar subqueries can be used in CASE expressions. The inner query returns the value 20, which is the department ID of the department whose location ID is 1800. The CASE expression in the outer query uses the result of the inner query to display the employee ID, last names, and a value of Canada or USA, depending on whether the department ID of the record retrieved by the outer query is 20 or not.//是USA,or Canada取决于由外查询的department_id记录返回是不是20   /./././//内连接是20 了,如果外连接是20则是Canada,如果不是20,则返回USAThe result of the preceding example follows:  EMPLOYEE_IDLAST_NAMELOCATI     100KingUSA  101KochharUSA  102De HaanUSA  103 HunoldUSA...  201HartsteinCanada   202FayCanada  206HigginsUSA  206Gietz USAScalar Subqueries: Examples (continued)  The second example in the slide demonstrates that scalar subqueries can be used in the ORDER BY clause. The example orders the output based on the DEPARTMENT_NAME by matching the DEPARTMENT_ID from the EMPLOYEES table with the DEPARTMENT_ID from the DEPARTMENTS table. This comparison in done in a scalar subquery in the ORDER BY clause. The result of the the second example follows:   The second example uses a correlated subquery. In a correlated subquery, the subquery references a column from a table referred to in the parent statement. Correlated subqueries are explained later in this lesson. 相关的子查询   相关子查询被用于 row-by-row 处理。对外查询的每一行，每个子查询被执行一次./././.GET|---- >candidate row from outer query    //从外查询中获得候选行.| ||      EXECUTE|   inner query using candidate row value //从内查询中获得候选行.| | |USE|----values from inner query to qualify  //or disqualify candidate rowCorrelated Subqueries  The Oracle Server performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement. /././././  Nested Subqueries Versus Correlated Subqueries     With a normal nested subquery, the inner SELECT query runs first and executes once, returning values to be used by the main query(这是对于一般的查询). A correlated subquery, however, executes once for each candidate row considered by the outer query. In other words, the inner query is driven by the outer query.  Nested Subquery Execution/././.    - The inner query executes first and finds a value.先内查询    - The outer query executes once, using the value from the inner query.//再外查询  Correlated Subquery Execution.,./././././    - Get a candidate row (fetched by the outer query).//从外查询中获得行    - Execute the inner query using the value of the candidate row. 用外查询获得的行执行内查询.    - Use the values resulting from the inner query to qualify or disqualify the candidate.使用从内查询中返回的值限定或不限定行.    - Repeat until no candidate row remains. //重复做直到没有行余下.相关子查询SELECT column1,column2, ...FROM table1 outerWHERE column1 operator      ( SELECTcolumn1,column2FROMtable2WHEREexpr1=//要有一个关联   outer.expr2);    子查询参考在父查询中的表的一个列Correlated Subqueries (continued)   A correlated subquery is one way of reading every row in a table and comparing values in each row against related data. It is used whenever a subquery must return a different result or set of results for each candidate row considered by the main query. In other words, you use a correlated subquery to answer a multipart question whose answer depends on the value in each row processed by the parent statement.    The Oracle Server performs a correlated subquery when the subquery references a column from a table in the parent query. (当一个子查询参考父查询表返回的列.)   Note: You can use the ANY and ALL operators in a correlated subquery. 使用相关子查询   找出所有的雇员，他们挣的薪水高于该部门的平均薪水SELECT last_name, salary, department_idFROM   employees outerWHERE  salary >|--->(SELECT AVG(salary) FROM   employees  WHERE  department_id =              outer.department_id) ;外查询中的行每被处理一次，内查询就求值一次Using Correlated Subqueries   The example in the slide determines which employees earn more than the average salary of their department. In this case, the correlated subquery specifically computes the average salary for each department.   Because both the outer query and inner query use the EMPLOYEES table in the FROM clause, an alias is given to EMPLOYEES in the outer SELECT statement, for clarity. Not only does the alias make the entire SELECT statement more readable, but without the alias the query would not work properly, because the inner statement would not be able to distinguish the inner table column from the outer table column.使用相关子查询    显示雇员的详细信息，这些雇员至少变换过两次工作SELECT e.employee_id, last_name,e.job_idFROM   employees e WHERE  2 <= (SELECT COUNT(*)             FROM   job_history              WHERE  employee_id = e.employee_id);Using Correlated Subqueries//对上面的例子进行分析  The example in the slide displays the details of those employees who have switched jobs at least twice. The Oracle Server evaluates a correlated subquery as follows:1. Select a row from the table specified in the outer query. This will be the current candidate row.2. Store the value of the column referenced in the subquery from this candidate row. (In the example in the slide, the column referenced in the subquery is E.EMPLOYEE_ID.)//从候选列中存储子查询中引用列的值,,子查询的引用列值:E.EMPLOYEE_ID3. Perform the subquery with its condition referencing the value from the outer query’s candidate row//计算内查询,将满足条件的count(*)找出来. (In the example in the slide, group function COUNT(*) is evaluated based on the value of the E.EMPLOYEE_ID column obtained in step 2.) e.employee_id的值从step 2得来.4. Evaluate the WHERE clause of the outer query on the basis of results of the subquery performed in step 3. This is determines if the candidate row is selected for output. (In the example, the  number of times an employee has switched jobs, evaluated by the subquery, is compared with 2 in the WHERE clause of the outer query. If the condition is satisfied, that employee record is displayed.)//将选出来的count(*)与2对比,如果>=则显示,否则不显示.5. Repeat the procedure for the next candidate row of the table, and so on until all the rows in the table have been processed.   The correlation is established by using an element from the outer query in the subquery. In this example, the correlation is established by the statement EMPLOYEE_ID = E.EMPLOYEE_ID in which you compare EMPLOYEE_ID from the table in the subquery with the EMPLOYEE_ID from the table in the outer query.使用 EXISTS 操作:: EXISTS 操作对在子查询的结果集中存在的行进行检验:: 如果一个子查询行值被找到：- 在内查询中的搜索不再继续././././.- 条件被标记为 TRUE:: 如果一个子查询行值未找到：- 条件被标记为 FALSE- 在内查询中的搜索继续The EXISTS Operator  With nesting SELECT statements, all logical operators are valid. In addition, you can use the EXISTS operator. This operator is frequently used with correlated subqueries to test whether a value retrieved by the outer query exists in the results set of the values retrieved by the inner query. If the subquery returns at least one row, the operator returns TRUE. If the value does not exist, it returns FALSE. 如果子查询返回至少一行,则操作返回TRUE,如果没有值返回,则返回FALSEAccordingly, NOT EXISTS tests whether a value retrieved by the outer query is not a part of  the results set of the values retrieved by the inner query.使用 EXISTS 操作查找至少有一个雇员的经理SELECT employee_id, last_name, job_id, department_idFROM   employees outerWHERE  EXISTS ( SELECT 'X'//如果返回X,则TRUE,否则FALSE.最后看是不是TRUE,即返回X                 FROM   employees的即为满足条件的.                 WHERE  manager_id =                         outer.employee_id);EMPLOYEE_ID LAST_NAME                 JOB_ID     DEPARTMENT_ID ----------- ------------------------- ---------- -------------         100 King                      AD_PRES               90        101 Kochhar                   AD_VP                 90        102 De Haan                   AD_VP                 90           103 Hunold                    IT_PROG               60          108 Greenberg                 FI_MGR               100         114 Raphaely                  PU_MAN                30        120 Weiss                     ST_MAN                50        121 Fripp                     ST_MAN                50        122 Kaufling                  ST_MAN                50        123 Vollman                   ST_MAN                50        124 Mourgos                   ST_MAN                50...//   解析一下::只要manager_id在employee_id中就显示,即是它的经理.SQL> select distinct manager_id  2  FROM   employees  3  WHERE  manager_id IS NOT NULL;MANAGER_ID----------       100       101       102       103       108       114       120       121       122       123       124       145       146       147       148       149       201       205SQL> select 'X' from dual;'-X   SELECT employee_id,last_name,job_id,department_id   FROM   employees    WHERE  employee_id IN (SELECT manager_id                          FROM   employees                          WHERE  manager_id IS NOT NULL);Using the EXISTS Operator    使用下面的条件，当至少找到一个经理号和雇员号相匹配的记录时，EXISTS 操作确保在内查询中的搜索不再继续：   WHERE manager_id = outer.employee_id.  Note that the inner SELECT query does not need to return a specific value(内查询不必找到确切的值), so a constant(常量也可以选择) can be selected. From a performance standpoint, it is faster to select a constant than a column.  Note: Having EMPLOYEE_ID in the SELECT clause of the inner query causes a table scan for that column. Replacing it with the literal X, or any constant, improves performance. This is more efficient than using the IN operator.   A IN construct can be used as an alternative for a EXISTS operator, as shown in the following example:    SELECT employee_id,last_name,job_id,department_id   FROM   employees    WHERE  employee_id IN (SELECT manager_id                          FROM   employees                          WHERE  manager_id IS NOT NULL);使用 NOT EXISTS 操作找出所有的没有任何雇员的部门SELECT department_id, department_nameFROM departments dWHERE NOT EXISTS (SELECT 'X'                  FROM   employees                  WHERE  department_id //要有一个联联                         = d.department_id);DEPARTMENT_ID DEPARTMENT_NAME------------- ------------------------------          120 Treasury          130 Corporate Tax          140 Control And Credit...可以得出以下是未选定行.SQL> select * from employees  2  where department_id=120;未选定行SQL> select * from employees  2  where department_id=130;未选定行...Using the NOT EXISTS Operator Alternative Solution   A NOT IN construct can be used as an alternative for a NOT EXISTS operator, as shown in the following example.    SELECT department_id, department_name   FROM   departments    WHERE  department_id NOT IN (SELECT department_id                                FROM   employees);   However, NOT IN evaluates to FALSE if any member of the set is a NULL value. 如果集合的任何成员是NULL值,NOT IN 的值是FALSE.因此,即使departments表中满足WHERE条件的行,你的查询将不会返回任何行.Therefore, your query will not return any rows even if there are rows in the departments table that satisfy the WHERE condition. 相关 UPDATEUPDATE table1 alias1SET    column = (SELECT expression                 FROM   table2 alias2                 WHERE  alias1.column =                            alias2.column);用一个相关子查询来更新在一个表中的行，该表基于另一个表中的行Correlated UPDATE   In the case of the UPDATE statement, you can use a correlated subquery to update rows in one table based on rows from another table.相关UPDATE:: 用一个附加的列来存储部门名称，反向规格化 EMPLOYEES 表:: 用相关更新填充表ALTER TABLE employeesADD(department_name VARCHAR2(30));UPDATE employees eSET    department_name =               (SELECT department_name        FROM   departments d       WHERE  e.department_id = d.department_id);//要有一个关联Correlated UPDATE (continued)   The example in the slide denormalizes the EMPLOYEES table by adding a column to store the department_name and then populates the table by using a correlated update.   Here is another example for a correlated update.Problem Statement  Use a correlated subquery to update rows in the EMPLOYEES table based on rows from the REWARDS table:././././././UPDATE employeesSET    salary = (SELECT employees.salary + rewards.pay_raise                 FROM   rewards                 WHERE  employee_id  =  employees.employee_id                 AND   payraise_date =                       (SELECT MAX(payraise_date)                        FROM   rewards                       WHERE  employee_id = employees.employee_id))WHERE  employees.employee_id IN    (SELECT employee_id         FROM   rewards);  This example uses the REWARDS table. The REWARDS table has the columns EMPLOYEE_ID, PAY_RAISE, and PAYRAISE_DATE. Every time an employee gets a pay raise, a record with the details of the employee ID, the amount of the pay raise, and the date of receipt of the pay raise is inserted into the REWARDS table. The REWARDS table can contain more than one record for an employee. The PAYRAISE _DATE column is used to identify the most recent pay raise received by an employee.  In the example, the SALARY column in the EMPLOYEES table is updated to reflect the latest pay raise received by the employee. This is done by adding the current salary of the employee with the corresponding pay raise from the REWARDS table. 相关 DELETEDELETE FROM table1 alias1 WHERE  column operator (SELECT expression  FROM   table2 alias2  WHERE  alias1.column = alias2.column);  用一个相关子查询删除表中的行，该表基于另一个表中的行Correlated DELETE  In the case of a DELETE statement, you can use a correlated subquery to delete only those rows that also exist in another table. If you decide that you will maintain only the last four job history records in the JOB_HISTORY table, then when an employee transfers to a fifth job, you delete the oldest JOB_HISTORY row by looking up the JOB_HISTORY table for the MIN(START_DATE)for the employee. The following code illustrates how the preceding operation can be performed using a correlated DELETE:   DELETE FROM job_history JH   WHERE  employee_id =         (SELECT employee_id           FROM   employees E          WHERE  JH.employee_id = E.employee_id././././要有一个关联          AND    start_date =                (SELECT MIN(start_date)                   FROM   job_history JH                 WHERE  JH.employee_id = E.employee_id)//关联                 AND 5 >  (SELECT COUNT(*)                             FROM   job_history JH                           WHERE  JH.employee_id = E.employee_id//关联                           GROUP  BY employee_id                           HAVING COUNT(*) >= 4));相关删除 DELETE   用一个相关子查询删除哪些在 EMPLOYEES 表和 EMP_HISTORY 表中的 employee_id 列值相同的行DELETE FROM employees EWHERE employee_id =             (SELECT employee_id            FROM   emp_history             WHERE  employee_id = E.employee_id);Correlated DELETE (continued)ExampleTwo tables are used in this example. They are:- The EMPLOYEES table, which gives details of all the current employees- The EMP_HISTORY table, which gives details of previous employees  EMP_HISTORY contains data regarding previous employees, so it would be erroneous if the same employee’s record existed in both the EMPLOYEES and EMP_HISTORY tables. You can delete such erroneous records by using the correlated subquery shown in the slide.WITH子句:: 当一个查询块在一个复杂的查询中出现多次时，使用 WITH 子句，能够在 SELECT 语句中多次使用相同查询块:: WITH 子句取回查询块的结果，并且将它存在用户的临时表空间中:: WITH 子句可以改善性能The WITH clause  Using the WITH clause, you can define a query block before using it in a query. The WITH clause (formally known as subquery_factoring_clause) enables you to reuse the same query block in a SELECT statement when it occurs more than once within a complex query. This is particularly useful when a query has many references to the same query block and there are joins and aggregations.   Using the WITH clause, you can reuse the same query when it is high cost to evaluate the query block and it occurs more than once within a complex query. Using the WITH clause, the Oracle Server retrieves the results of a query block and stores it in the user’s temporary tablespace. This can improve performance. WITH Clause Benefits- Makes the query easy to read- Evaluates a clause only once, even if it appears multiple times in the query,     thereby enhancing performance WITH 子句: 例子   用 WITH 子句，写一个查询来显示部门名称和该部门的合计薪水，哪些人的合计薪水高于各部门的平均薪水WITH dept_costs  AS (   SELECT  d.department_name, SUM(e.salary) AS dept_total   FROM    employees e, departments d   WHERE   e.department_id = d.department_id   GROUP BY d.department_name),avg_cost    AS (   SELECT SUM(dept_total)/COUNT(*) AS dept_avg   FROM   dept_costs)SELECT * FROM   dept_costs WHERE  dept_total >        (SELECT dept_avg          FROM avg_cost)ORDER BY department_name;  The  problem in the slide would require the following intermediate calculations:1.Calculate the total salary for every department, and store the result using a WITH clause.2.Calculate the average salary across departments, and store the result using a WITH clause.3.Compare the total salary calculated in the first step with the average salary calculated in the second step. If the total salary for a particular department is greater than the average salary across departments, display the department name and the total salary for that department. WITH Clause: Example (continued)   The SQL code in the slide is an example of a situation in which you can improve performance and write SQL more simply by using the WITH clause. The query creates the query names DEPT_COSTS and AVG_COST and then uses them in the body of the main query. Internally, the WITH clause is resolved either as an in-line view or a temporary table. The optimizer chooses the appropriate resolution depending on the cost or benefit of temporarily storing the results of the WITH clause.  Note: A subquery in the FROM clause of a SELECT statement is also called an in-line view.(内部视图) The output generated by the SQL code on the slide will be as follows:DEPARTMENT_NAME                DEPT_TOTAL------------------------------ ----------Sales                              304500Shipping                           156400The WITH Clause Usage Notes  -- It is used only with SELECT statements  -- A query name is visible to all WITH element query blocks (including their subquery blocks) defined after it and the main query block itself (including its subquery blocks).  -- When the query name is the same as an existing table name, the parser searches from the inside out, the query block name takes precedence over the table name.  -- The WITH clause can hold more than one query. Each query is then separated by a comma.SUMMARY在本课中, 您应该已经学会如何：:: 返回多于一列的多列子查询:: 多列比较可以成对或非成对地进行:: 一个多列子查询也能够被用于一个 SELECT 语句的 FROM 子句:: 分级子查询在 Oracle9i 中得到了增强Summary   You can use multiple-column subqueries to combine multiple WHERE conditions into a single WHERE clause. Column comparisons in a multiple-column subquery can be pairwise comparisons or non-pairwise comparisons.  You can use a subquery to define a table to be operated on by a containing query.   Oracle 9i enhances the the uses of scalar subqueries. Scalar subqueries can now be used in:   : Condition and expression part of DECODE  and CASE   : All clauses of SELECT except GROUP BY     : SET clause and WHERE clause of UPDATE statement小结:: 无论何时一个子查询必须对每一个侯选行返回不同的结果，这时，相关子查询是有用的:: EXISTS 操作是测试值的存在性的布尔操作:: 相关子查询能够用于 SELECT, UPDATE, and DELETE 语句:: 在 SELECT 语句中你能够通过 WITH 子句多次使用相同的查询块Summary (continued)   The Oracle Server performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement. Using the WITH clause, you can reuse the same query when it is costly to reevaluate the query block and it occurs more than once within a complex query.************分级取回数据**************目标 完成本课后, 您应当能够执行下列操作：:: 解释分级查询的概念:: 创建一个树型结构的报告:: 格式化分级数据:: 从树型结构中去除分支 Lesson Aim  In this lesson, you learn how to use hierarchical queries to create tree-structured reports.(树型结构报告) EMPLOYEES 表中的例子数据Sample Data from the EMPLOYEES Table   Using hierarchical queries, you can retrieve data based on a natural hierarchical relationship between rows in a table. A relational database does not store records in a hierarchical way关系型数据库不能以分等级的方式存储. However, where a hierarchical relationship exists between the rows of a single table, a process called tree walking enables the hierarchy to be constructed. A hierarchical query is a method of reporting, in order, the branches of a tree.   Imagine a family tree with the eldest members of the family found close to the base or trunk of the tree and the youngest members representing branches of the tree. Branches can have their own branches, and so on.    A hierarchical query is possible when a relationship exists between rows in a table.当在一个表中存在行行之间的关系,分等级查询是有可能的. For example, in the slide, you see that employees with the job IDs of AD_VP, ST_MAN, SA_MAN, and MK_MAN report directly to the president of the company. We know this because the MANAGER_ID column of these records contain the employee ID 100, which belongs to the president (AD_PRES).    Note: Hierarchical trees are used in various fields such as human genealogy (family trees), livestock (breeding purposes), corporate management (management hierarchies), manufacturing (product assembly), evolutionary research (species development), and scientific research.自然树结构Natural Tree Structure    The EMPLOYEES table has a tree structure representing the management reporting line. The hierarchy can be created by looking at the relationship between equivalent values in the EMPLOYEE_ID and MANAGER_ID columns. This relationship can be exploited by joining the table to itself(这些表自连接将可以使用.). The MANAGER_ID column contains the employee number of the employee’s manager.  The parent-child relationship of a tree structure enables you to control:- The direction in which the hierarchy is walked- The starting point inside the hierarchy  Note: The slide displays an inverted tree structure of the management hierarchy of the employees in the EMPLOYEES table.分等级查询SELECT [LEVEL], column, expr...FROM   table[WHERE condition(s)][START WITH condition(s)][CONNECT BY PRIOR condition(s)] ;WHERE条件:expr comparison_operator(比较运算符) exprKeywords and Clauses(子句和关键字)  Hierarchical queries can be identified by the presence of the CONNECT BY and START WITH clauses.  In the syntax:SELECTIs the standard SELECT clause. LEVEL For each row returned by a hierarchical query, the LEVEL 为每一行返回分等级查询,pseudocolumn(列) returns 1 for a root row, 2 for a child of a root,and so on./././././FROM tableSpecifies the table, view, or snapshot containing the columns. You can select from only one table.(只能是一个表)WHERERestricts the rows returned by the query without affecting other rows ofthe hierarchy. (返回没有影响其它分等级行的查询)        conditionIs a comparison with expressions.(比较表达式条件)START WITHSpecifies the root rows of the hierarchy (where to start). This clause is required for a tree hierarchical query.指定层次的根行(从哪开始),这个子句对于树型分类查询是必须的.CONNECT BYSpecifies the columns in which the relationship between parent and child指定父子关系的列PRIORrows exist. This clause is required for a hierarchical query.行存在.对于分等级查询是必须的. The SELECT statement cannot contain a join or query from a view that contains a join.SELECT操作不能包括连接,或从一个包含连接的视图中查询遍历树起点:: 指定必须满足的条件:: 接受有效的条件 START WITH column1=value使用EMPLOYEES表,从名字是Kochhar的雇员开始...START WITH last_name='Kochhar'Walking the Tree(遍历树)  The row or rows to be used as the root of the tree are determined by the START WITH clause(行,或行S将作为树根决定于START WITH子句). The START WITH clause can be used in conjunction with any valid condition.START WITH子句在任何有效的条件连接使用 Examples Using the EMPLOYEES table, start with King, the president of the company.... START WITH manager_id IS NULL Using the EMPLOYEES table, start with employee Kochhar. A START WITH condition can contain a subquery.... START WITH employee_id = (SELECT employee_id                                  FROM    employees                                WHERE   last_name = 'Kochhar')  If the START WITH clause is omitted(忽略), the tree walk is started with all of the rows in the table as root rows(树的遍历将从表中所有的行,作为根开始遍历). If a WHERE clause is used, the walk is started with all the rows that satisfy the WHERE condition. This no longer reflects a true hierarchy.如果WHERE子句使用了,遍历将从所有的行开始,并且满足WHERE条件.这将不再返回树的层次. Note: The clauses CONNECT BY PRIOR and START WITH are not ANSI SQL standard.  Instructor Note    You may wish to add that multiple hierarchical outputs are generated if more than one row satisfies the START WITH condition.遍历树(有方向的查询)CONNECT BY PRIOR column1=column2从顶向下遍历,用employees表   父列是employee_id,子列是manager_id...CONNECT BY PRIOR employee_id=manager_id方向从顶向下---->Column1=Parent Key      Column2=Child Key从底向上---->Column1=Child Key     Column2=Parent Key Walking the Tree (continued)   The direction of the query, whether it is from parent to child or from child to parent, is determined by the CONNECT BY PRIOR column placement. The PRIOR operator refers to the parent row(PRIOR操作涉及到父行). To find the children of a parent row, the Oracle Server evaluates the PRIOR expression for the parent row and the other expressions for each row in the table(为了找到父行中的子行,ORACLE SERVER将PRIOR作用于父行,并且其它的表达式操作将用于表中的其它行,行的条件为真则说明子行在父行中). Rows for which the condition is true are the children of the parent. The Oracle Server always selects children by evaluating the CONNECT BY condition with respect to a current parent row.ExamplesWalk from the top down(自顶向下) using the EMPLOYEES table. Define a hierarchical relationship in which the EMPLOYEE_ID value of the parent row is equal to the MANAGER_ID value of the child row.... CONNECT BY PRIOR employee_id = manager_id Walk from the bottom up using the EMPLOYEES table. ... CONNECT BY PRIOR manager_id = employee_id  The PRIOR operator does not necessarily need to be coded immediately following the CONNECT BY(PRIOR操作符并不是非要直接跟随在CONNECT BY后). Thus, the following CONNECT BY PRIOR clause gives the same result(与先前的例子有相同的结果) as the one in the preceding example. ... CONNECT BY employee_id = PRIOR manager_id  Note: The CONNECT BY clause cannot contain a subquery. ././././.不能包含子查询遍历树:从底向上SELECT employee_id, last_name, job_id, manager_idFROM   employeesSTART  WITH  employee_id = 101CONNECT BY PRIOR manager_id = employee_id ;//从底向上EMPLOYEE_ID LAST_NAME                 JOB_ID     MANAGER_ID----------- ------------------------- ---------- ----------        101 Kochhar                   AD_VP             100        100 King                      AD_PRESWalking the Tree: From the Bottom Up   The example in the slide displays a list of managers starting with the employee whose employee ID is 101.Example  In the following example, EMPLOYEE_ID values are evaluated for the parent row and MANAGER_ID, and SALARY values are evaluated for the child rows. The PRIOR operator applies only to the EMPLOYEE_ID value.... CONNECT BY PRIOR employee_id = manager_idAND salary > 15000;SQL>  SELECT employee_id, last_name, job_id, manager_id  2   FROM   employees  3   START  WITH  employee_id = 101  4   CONNECT BY PRIOR manager_id = employee_id  5  and salary>15000;EMPLOYEE_ID LAST_NAME                 JOB_ID     MANAGER_ID----------- ------------------------- ---------- ----------        101 Kochhar                   AD_VP             100        100 King                      AD_PRES  To qualify as a child row, a row must have a MANAGER_ID value equal to the EMPLOYEE_ID value of the parent row and must have a SALARY value greater than $15,000.为了取得子行,要有列MANAGER_ID的值=父行中employee_id的值../././Instructor Note:: In the context of the second paragraph, you may wish to include that additional conditions added to the CONNECT BY PRIOR clause potentially eliminated the whole of the branch(潜在的限制树支), hence the EMPLOYEE_ID AND SALARY are evaluated for the parent row to determine if it is to be part of the output.SQL> SELECT  last_name||' reports to '|| PRIOR   last_name "Walk Top Down"  2  FROM    employees  3  start with employee_id=101  4  connect by prior manager_id=employee_id;Walk Top Down--------------------------------//插一下Kochhar reports toKing reports to KochharSQL> SELECT  last_name||' reports to '|| last_name "Walk Top Down"  2   FROM    employees  3   start with employee_id=101  4   connect by prior manager_id=employee_id;Walk Top Down--------------------------------------------------------------Kochhar reports to KochharKing reports to King遍历树:  从顶向下SELECT  last_name||' reports to '|| PRIOR   last_name "Walk Top Down"FROM    employeesSTART   WITH last_name = 'King'CONNECT BY PRIOR employee_id = manager_id ;Walking the Tree: From the Top Down  Walking from the top down, display the names of the employees and their manager. Use employee King as the starting point. Print only one column.Walk Top Down--------------------------------King reports toKing reports toKochhar reports to KingGreenberg reports to KochharFaviet reports to GreenbergChen reports to GreenbergSciarra reports to GreenbergUrman reports to GreenbergPopp reports to GreenbergWhalen reports to KochharMavris reports to KochharSELECT  last_name||' reports to '|| last_name "Walk Top Down"FROM    employeesSTART   WITH last_name = 'King'CONNECT BY PRIOR employee_id = manager_id ;Walk Top Down------------------------------------------King reports to KingKing reports to KingKochhar reports to KochharGreenberg reports to GreenbergFaviet reports to FavietChen reports to ChenSciarra reports to SciarraUrman reports to UrmanPopp reports to PoppWhalen reports to WhalenMavris reports to Mavris...用LEVEL伪列将行分等级Ranking Rows with the LEVEL Pseudocolumn  You can explicitly show the rank or level of a row in the hierarchy by using the LEVEL pseudocolumn(伪列). This will make your report more readable(这将使你的报告更容易读).  The forks where one or more branches split away from a larger branch are called nodes, and the very end of a branch is called a leaf, or leaf node. The diagram in the slide shows the nodes of the inverted tree with their LEVEL values. For example, employee Higgens is a parent and a child, while employee Davies is a child and a leaf.  The LEVEL PseudocolumnValueLevel1A root node(根)2 A child of a root node(根的孩子)3 A child of a child, and so on(根的孩子的孩子...) Note: A root node is the highest node within an inverted tree. A child node is any nonroot node. A parent node is any node that has children. A leaf node is any node without children. The number of levels returned by a hierarchical query may be limited by available user memory. In the slide, King is the root or parent (LEVEL = 1). Kochhar, De Hann, Mourgos, Zlotkey, Hartstein, Higgens, and Hunold are children and also parents (LEVEL = 2). Whalen, Rajs, Davies, Matos, Vargas, Gietz, Ernst, Lorentz, Abel, Taylor, Grant, and Fay are children and leaves. (LEVEL = 3 and LEVEL = 4)用 LEVEL 和 LPAD 格式化分级报告  创建一个报告显示公司的管理层，从最高级别开始，缩进下面跟随的级别COLUMN org_chart FORMAT A18SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_')        AS org_chartFROM   employees START WITH last_name='King' CONNECT BY PRIOR employee_id=manager_id;Formatting Hierarchical Reports Using LEVEL The nodes in a tree are assigned level numbers from the root. Use the LPAD function in conjunction with the pseudocolumn LEVEL to display a hierarchical report as an indented tree.(交错树状) In the example on the slide:   : LPAD(char1,n [,char2]) returns char1, left-padded to length n with the sequence of characters in char2. The argument n is the total length of the return value as it is displayed on your terminal screen.   : LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_')defines the display format.   : char1 is the LAST_NAME , n the total length of the return value, is length of the LAST_NAME +(LEVEL*2)-2 ,and  char2  is '_'. In other words, this tells SQL to take the LAST_NAME and left-pad it with the '_' character till the length of the resultant string is equal to the value determined by LENGTH(last_name)+(LEVEL*2)-2.   For King, LEVEL = 1. Hence, (2 * 1) - 2 = 2 - 2 = 0. So King does not get padded with any '_' character and is displayed in column 1.  For Kochhar, LEVEL = 2. Hence, (2 * 2) - 2 = 4 - 2 = 2 . So Kochhar gets padded with 2 '_' characters and is displayed indented. The rest of the records in the EMPLOYEES table are displayed similarly. Formatting Hierarchical Reports Using LEVEL (continued)King__Kochhar____Greenberg______Faviet______Chen______Sciarra______Urman______Popp____Whalen____MavrisORG_CHART--------------------____Baer____Higgins______Gietz__De Haan____Hunold______Ernst______Austin______Pataballa______Lorentz修剪分支用 WHERE 子句 用 CONNECT BY 子句去除一个结点(node)叶子还要去除一个分支(node,叶子都不要了)Where last_name !='Higgins'CONNECT BY PRIORemployee_id=manager_idAND last_name !='Higgins'范围:小    范围:大Pruning Branches  You can use the WHERE and CONNECT BY clauses to prune the tree; that is, to control which nodes or rows are displayed(控制哪些节点或行S不被显示). The predicate you use acts as a Boolean condition.ExamplesStarting at the root, walk from the top down, and eliminate employee Higgins in the result, but process(保留) the child rows.     SELECT  department_id, employee_id,last_name, job_id, salary     FROM    employees     WHERE   last_name  != 'Higgins'     START   WITH manager_id IS NULL     CONNECT BY PRIOR employee_id = manager_id;  Starting at the root, walk from the top down, and eliminate employee Higgins and all child rows.(除去整个分支)     SELECT  department_id, employee_id,last_name, job_id, salary     FROM    employees     START   WITH manager_id IS NULL     CONNECT BY PRIOR employee_id = manager_id     AND     last_name != 'Higgins';

:: 写多列子查询:: 在返回空值时描述并解释子查询的行为:: 写一个在 FROM 子句中的子查询:: SQL 中使用分级子查询:: 描述能够用相关子查询解决的问题类型:: 写相关子查询:: 用相关子查询更新和删除行:: 使用 EXISTS 和 NOT EXISTS 操作:: 使用 WITH 子句Lesson Aim   In this lesson, you learn how to write multiple-column subqueries and subqueries in the FROM clause of a SELECT statement. You also learn how to solve problems by using scalar, correlated subqueries and the WITH clause.什么是子查询? 一个子查询是一个嵌入 SELECT 语句中的另一个 SQL 语句的子句select ...   主查询---->  from   ...where  ... (select ... from   ...      <-----子查询 where  ...)What Is a Subquery?  A subquery is a SELECT statement that is embedded in a clause of another SQL statement, called the parent statement.内查询把查询结果给外查询 The subquery (inner query) returns a value that is used by the parent statement. Using a nested subquery is equivalent to performing two sequential queries and using the result of the inner query as the search value in the outer query (main query).  Subqueries can be used for the following purposes:     :: To provide values for conditions in WHERE, HAVING, and START WITH clauses of SELECT statements    :: To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement/././././    :: To define the set of rows to be included in a view or snapshot in a CREATE VIEW or CREATE SNAPSHOT statement/./././同上    :: To define one or more values to be assigned to existing rows in an UPDATE statement////同上    :: To define a table to be operated on by a containing query. (You do this by placing the subquery in the FROM clause. This can be done in INSERT, UPDATE, and DELETE statements as well.)/,.,.FROM子查询  Note: A subquery is evaluated once for the entire parent statement.先执行内查询,返回值给外查询,再执行主查询.子查询SELECTselect_listFROMtableWHEREexpr operator (SELECT select_list         FROM  table);:: 子查询 (内嵌查询) 在主查询中执行一次:: 子查询的结果被用于主查询 (外查询)Subqueries  You can build powerful statements out of simple ones by using subqueries. Subqueries can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself or some other table. Subqueries are very useful for writing SQL statements that need values based on one or more unknown conditional values. In the syntax:././././.operatorincludes a comparison operator such as >, =, or IN  Note: Comparison operators fall into two classes: (比较运算)single-row operators (>, =, >=, <, <>, <=)multiple-row operators (IN, ANY, ALL).  The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The inner and outer queries can retrieve data from either the same table or different tables.(叫做内嵌select,子select,内部select)使用子查询SELECT last_nameFROM   employees         10500WHERE  salary >    <-------------|                (SELECT salary   |                 FROM   employees                 WHERE  employee_id = 149) ;Using a Subquery  //对上面的解释  In the example in the slide, the inner query returns the salary of the employee with employee number 149. The outer query uses the result of the inner query to display the names of all the employees who earn more than this amount.Example: Display the names of all employees who earn less than the average salary in the company.   SELECT last_name, job_id, salary   FROM   employees   WHERE  salary < (SELECT AVG(salary)                    FROM   employees);多列子查询  主查询的每行与一个多行多列子查询的值比较Main query <----------|WHERE(manager_id,department_id) IN<-------|   ||   | <-----||   |       ||   |Subquery       ||   |10090_______||   |10260________|   |12450____________|Multiple-Column Subqueries So far you have written single-row subqueries and multiple-row subqueries where only one column is returned by the inner SELECT statement and this is used to evaluate the expression in the parent select statement. If you want to compare two or more columns, you must write a compound WHERE clause using logical operators(如果想要比较两行或更多行你要使用逻辑运算符写一个混合的WHERE子句). Using multiple-column subqueries, you can combine duplicate WHERE conditions into a single WHERE clause.(使用混合列子查询可以把多个WHERE条件合到一个WHERE子句.)  SyntaxSELECT column,column, ...FROM tableWHERE (column,column, ...) IN   (SELECT column,column, ...    FROM table    WHERE condition);   The graphic in the slide illustrates that the values of the MANAGER_ID and DEPARTMENT_ID from the main query are being compared with the MANAGER_ID and DEPARTMENT_ID  values retrieved by the subquery. Since the number of columns that are being compared are more than one, the example qualifies as a multiple-column subquery.列比较在一个多列子查询中的列比较能够被：:: 成对地比较:: 非成对的比较 Pairwise versus Nonpairwise Comparisons成对,非成对比较      Column comparisons in a multiple-column subquery can be pairwise comparisons or nonpairwise comparisons. /././././.在select语句的每个条件行都要有相同的列.   In the example on the next slide, a pairwise comparison was executed in the WHERE clause. Each candidate row in the SELECT statement must have both the same MANAGER_ID column and the DEPARTMENT_ID as the employee with the EMPLOYEE_ID 178 or 174.      A multiple-column subquery can also be a nonpairwise comparison. In a nonpairwise comparison, each of the columns from the WHERE clause of the parent SELECT statement are individually compared to multiple values retrieved by the inner select statement. The individual columns can match any of the values retrieved by the inner select statement. But collectively, all the multiple conditions of the main SELECT statement must be satisfied for the row to be displayed. The example on the next page illustrates a nonpairwise comparison.成对比较子查询   显示雇员的细节，这些雇员被同一个经理管理，并且，工作在同一个部门，具有 EMPLOYEE_ID 178 或 174SELECTemployee_id, manager_id, department_idFROMemployeesWHERE  (manager_id, department_id) IN                      (SELECT manager_id, department_id                       FROM   employees                       WHERE  employee_id IN (178,174))ANDemployee_id NOT IN (178,174);SQL> select manager_id,department_id  2  from employees  3  where employee_id in (178,174);MANAGER_ID DEPARTMENT_ID---------- -------------       149            80       149SQL> SELECT     employee_id, manager_id, department_id  2  FROM       employees  3  WHERE  (manager_id, department_id) IN  4                        (SELECT manager_id, department_id  5                         FROM   employees  6                         WHERE  employee_id IN (178,174))  7  AND        employee_id NOT IN (178,174);EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID----------- ---------- -------------        179        149            80        177        149            80        176        149            80        175        149            80SQL> SELECT     employee_id, manager_id, department_id  2  FROM       employees  3  WHERE  (manager_id, department_id) IN  4                        (SELECT manager_id, department_id  5                         FROM   employees  6                         WHERE  employee_id IN (178,174))  7  ;EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID----------- ---------- -------------        179        149            80        177        149            80        176        149            80        175        149            80        174        149            80/./././.Pairwise Comparison Subquery//   The example in the slide is that of a multiple-column subquery because the subquery returns more than one column(子查询返回值多于一行). It compares the values in the MANAGER_ID column and the DEPARTMENT_ID column of each row in the EMPLOYEES table with the values in the MANAGER_ID column and the DEPARTMENT_ID column for the employees with the EMPLOYEE_ID 178 or 174.  First, the subquery to retrieve the MANAGER_ID and DEPARTMENT_ID values for the employees with the EMPLOYEE_ID 178 or 174 is executed. These values are compared with the MANAGER_ID column and the DEPARTMENT_ID column of each row in the EMPLOYEES table. If the values match, the row is displayed. In the output,  the records of the employees with the EMPLOYEE_ID 178 or 174 will not be displayed. The output of the query in the slide follows.非成对比较子查询    显示被同一个经理管理，具有 EMPLOYEE_ID 174 或 141 的雇员；并且，工作在同一个部门，具有 EMPLOYEE_ID 174 或 141 的雇员的详细信息SELECT  employee_id, manager_id, department_id 3/FROM    employeesWHERE   manager_id IN                   (SELECT  manager_id1/                   FROM    employees                   WHERE   employee_id IN (174,141))AND     department_id IN                   (SELECT  department_id2/                   FROM    employees                   WHERE   employee_id IN (174,141))ANDemployee_id NOT IN(174,141);返回的department_id值和manager_id值与departments表中的每一行进行比较.要两个值同时都满足才display.Nonpairwise Comparison Subquery   The example shows a nonpairwise comparison of the columns. It displays the EMPLOYEE_ID, MANAGER_ID, and DEPARTMENT_ID of any employee whose manager ID matches any of the manager IDs of employees whose employee IDs are either 174 or 141 and DEPARTMENT_ID match any of the department IDs of employees whose employee IDs are either 174 or 141.   First, the subquery to retrieve the MANAGER_ID values for the employees with the EMPLOYEE_ID 174 or 141 is executed. Similarly, the second subquery to retrieve the DEPARTMENT_ID values for the employees with the EMPLOYEE_ID 174 or 141 is executed. the retrived values of the MANAGER_ID and DEPARTMENT_ID columns are compared with the MANAGER_ID and DEPARTMENT_ID column for each row in the EMPLOYEES table. If the MANAGER_ID column of the row in the EMPLOYEES table matches with any of the values of the MANAGER_ID retrieved by the inner subquery and if the DEPARTMENT_ID column of the row in the EMPLOYEES table matches with any of the values of the DEPARTMENT_ID retrieved by the second subquery, the record is displayed. The output of the query in the slide follows.    EMPLOYEE_IDMANAGER_IDDEPARTMENT_ID    142       124 50    143       124 50    144       124 50    176       149 80在 FROM 子句中使用子查询SELECT  a.last_name, a.salary,         a.department_id, b.salavg//必须是b 表中的'列'FROM    employees a, (SELECT   department_id,                       AVG(salary) salavg                      FROM     employees                      GROUP BY department_id) bWHERE   a.department_id = b.department_idAND     a.salary > b.salavg;Using a Subquery in the FROM Clause    You can use a subquery in the FROM clause of a SELECT statement, which is very similar to how views are used. A subquery in the FROM clause of a SELECT statement is also called an inline view(内部视图). A subquery in the FROM clause of a SELECT statement defines a data source for that particular SELECT statement, and only that SELECT statement. The example on the slide displays employee last names, salaries, department numbers, and average salaries for all the employees who earn more than the average salary in their department. The subquery in the FROM clause is named b, and the outer query references the SALAVG column using this alias.//注意表的别名.分级子查询表达式:: 一个分级子查询表达式是一个从一行中返回确切的一个列值的子查询:: 在 Oracle8i 中，分级子查询仅在一些有限情况的情况下被支持，例如：     - SELECT 语句 (FROM 和 WHERE 子句)     - 在一个 INSERT 语句中的VALUES 表中:: 在 Oracle9i 中，分级子查询能够被用于：     - DECODE  and CASE 的条件和表达式部分     - 除 GROUP BY 以外所有的 SELECT 子句Scalar Subqueries in SQL   A subquery that returns exactly one column value from one row is also referred to as a scalar subquery(一个分级子查询表达式是一个从一行中返回确切的一个列值的子查询.) Multiple-column subqueries written to compare two or more columns, using a compound WHERE clause and logical operators, do not qualify as scalar subqueries.././././././如果子查询返回0行,分级子查询表达式是NULL,如果子查询返回多行,Oracle Server返回ERROR. If the subquery returns 0 rows, the value of the scalar subquery expression is NULL. If the subquery returns more than one row, the Oracle Server returns an error.  The value of the scalar subquery expression is the value of the select list item of the subquery. If the subquery returns 0 rows, the value of the scalar subquery expression is NULL. If the subquery returns more than one row, the Oracle Server returns an error. The Oracle Server has always supported the usage of a scalar subquery in a  SELECT statement. The usage of scalar subqueries has been enhanced in Oracle9i. You can now use scalar subqueries in:   - Condition and expression part of DECODE and CASE   - All clauses of SELECT except GROUP BY   - In the left-hand side of the operator in the SET clause and WHERE clause of UPDATE statement   However, scalar subqueries are not valid expressions in the following places:   - As default values for columns and hash expressions for clusters   - In the RETURNING clause of DML statements   - As the basis of a function-based index基本函数索引   - In GROUP BY clauses, CHECK constraints,  WHEN conditions///./././   - HAVING clauses./././   - In START WITH and CONNECT BY clauses   - In statements that are unrelated to queries, such as CREATE PROFILE分级子查询: 例子  在CASE表达式的分级子查询.SELECT employee_id, last_name,       (CASE20 <----|          WHEN department_id =|                (SELECT deaprtment_id FROM deaprtments                 WHERE location_id=1800)        THEN 'Canada' ELSE 'USA' END) locationFROM   employees;......EMPLOYEE_ID LAST_NAME                 LOCATI----------- ------------------------- ------        199 Grant                     USA        200 Whalen                    USA        201 Hartstein                 Canada        202 Fay                       Canada        203 Mavris                    USA        204 Baer                      USA...   在 ORDER BY 子查询中的分级子查询SELECT   employee_id, last_nameFROM     employees e//两个表ORDER BY(SELECT department_name//用deaprtments这个表的department_name排序 FROM departments d WHERE e.department_id = d.department_id);Scalar Subqueries: Examples  The first example in the slide demonstrates(认证) that scalar subqueries can be used in CASE expressions. The inner query returns the value 20, which is the department ID of the department whose location ID is 1800. The CASE expression in the outer query uses the result of the inner query to display the employee ID, last names, and a value of Canada or USA, depending on whether the department ID of the record retrieved by the outer query is 20 or not.//是USA,or Canada取决于由外查询的department_id记录返回是不是20   /./././//内连接是20 了,如果外连接是20则是Canada,如果不是20,则返回USAThe result of the preceding example follows:  EMPLOYEE_IDLAST_NAMELOCATI     100KingUSA  101KochharUSA  102De HaanUSA  103 HunoldUSA...  201HartsteinCanada   202FayCanada  206HigginsUSA  206Gietz USAScalar Subqueries: Examples (continued)  The second example in the slide demonstrates that scalar subqueries can be used in the ORDER BY clause. The example orders the output based on the DEPARTMENT_NAME by matching the DEPARTMENT_ID from the EMPLOYEES table with the DEPARTMENT_ID from the DEPARTMENTS table. This comparison in done in a scalar subquery in the ORDER BY clause. The result of the the second example follows:   The second example uses a correlated subquery. In a correlated subquery, the subquery references a column from a table referred to in the parent statement. Correlated subqueries are explained later in this lesson. 相关的子查询   相关子查询被用于 row-by-row 处理。对外查询的每一行，每个子查询被执行一次./././.GET|---- >candidate row from outer query    //从外查询中获得候选行.| ||      EXECUTE|   inner query using candidate row value //从内查询中获得候选行.| | |USE|----values from inner query to qualify  //or disqualify candidate rowCorrelated Subqueries  The Oracle Server performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement. /././././  Nested Subqueries Versus Correlated Subqueries     With a normal nested subquery, the inner SELECT query runs first and executes once, returning values to be used by the main query(这是对于一般的查询). A correlated subquery, however, executes once for each candidate row considered by the outer query. In other words, the inner query is driven by the outer query.  Nested Subquery Execution/././.    - The inner query executes first and finds a value.先内查询    - The outer query executes once, using the value from the inner query.//再外查询  Correlated Subquery Execution.,./././././    - Get a candidate row (fetched by the outer query).//从外查询中获得行    - Execute the inner query using the value of the candidate row. 用外查询获得的行执行内查询.    - Use the values resulting from the inner query to qualify or disqualify the candidate.使用从内查询中返回的值限定或不限定行.    - Repeat until no candidate row remains. //重复做直到没有行余下.相关子查询SELECT column1,column2, ...FROM table1 outerWHERE column1 operator      ( SELECTcolumn1,column2FROMtable2WHEREexpr1=//要有一个关联   outer.expr2);    子查询参考在父查询中的表的一个列Correlated Subqueries (continued)   A correlated subquery is one way of reading every row in a table and comparing values in each row against related data. It is used whenever a subquery must return a different result or set of results for each candidate row considered by the main query. In other words, you use a correlated subquery to answer a multipart question whose answer depends on the value in each row processed by the parent statement.    The Oracle Server performs a correlated subquery when the subquery references a column from a table in the parent query. (当一个子查询参考父查询表返回的列.)   Note: You can use the ANY and ALL operators in a correlated subquery. 使用相关子查询   找出所有的雇员，他们挣的薪水高于该部门的平均薪水SELECT last_name, salary, department_idFROM   employees outerWHERE  salary >|--->(SELECT AVG(salary) FROM   employees  WHERE  department_id =              outer.department_id) ;外查询中的行每被处理一次，内查询就求值一次Using Correlated Subqueries   The example in the slide determines which employees earn more than the average salary of their department. In this case, the correlated subquery specifically computes the average salary for each department.   Because both the outer query and inner query use the EMPLOYEES table in the FROM clause, an alias is given to EMPLOYEES in the outer SELECT statement, for clarity. Not only does the alias make the entire SELECT statement more readable, but without the alias the query would not work properly, because the inner statement would not be able to distinguish the inner table column from the outer table column.使用相关子查询    显示雇员的详细信息，这些雇员至少变换过两次工作SELECT e.employee_id, last_name,e.job_idFROM   employees e WHERE  2 <= (SELECT COUNT(*)             FROM   job_history              WHERE  employee_id = e.employee_id);Using Correlated Subqueries//对上面的例子进行分析  The example in the slide displays the details of those employees who have switched jobs at least twice. The Oracle Server evaluates a correlated subquery as follows:1. Select a row from the table specified in the outer query. This will be the current candidate row.2. Store the value of the column referenced in the subquery from this candidate row. (In the example in the slide, the column referenced in the subquery is E.EMPLOYEE_ID.)//从候选列中存储子查询中引用列的值,,子查询的引用列值:E.EMPLOYEE_ID3. Perform the subquery with its condition referencing the value from the outer query’s candidate row//计算内查询,将满足条件的count(*)找出来. (In the example in the slide, group function COUNT(*) is evaluated based on the value of the E.EMPLOYEE_ID column obtained in step 2.) e.employee_id的值从step 2得来.4. Evaluate the WHERE clause of the outer query on the basis of results of the subquery performed in step 3. This is determines if the candidate row is selected for output. (In the example, the  number of times an employee has switched jobs, evaluated by the subquery, is compared with 2 in the WHERE clause of the outer query. If the condition is satisfied, that employee record is displayed.)//将选出来的count(*)与2对比,如果>=则显示,否则不显示.5. Repeat the procedure for the next candidate row of the table, and so on until all the rows in the table have been processed.   The correlation is established by using an element from the outer query in the subquery. In this example, the correlation is established by the statement EMPLOYEE_ID = E.EMPLOYEE_ID in which you compare EMPLOYEE_ID from the table in the subquery with the EMPLOYEE_ID from the table in the outer query.使用 EXISTS 操作:: EXISTS 操作对在子查询的结果集中存在的行进行检验:: 如果一个子查询行值被找到：- 在内查询中的搜索不再继续././././.- 条件被标记为 TRUE:: 如果一个子查询行值未找到：- 条件被标记为 FALSE- 在内查询中的搜索继续The EXISTS Operator  With nesting SELECT statements, all logical operators are valid. In addition, you can use the EXISTS operator. This operator is frequently used with correlated subqueries to test whether a value retrieved by the outer query exists in the results set of the values retrieved by the inner query. If the subquery returns at least one row, the operator returns TRUE. If the value does not exist, it returns FALSE. 如果子查询返回至少一行,则操作返回TRUE,如果没有值返回,则返回FALSEAccordingly, NOT EXISTS tests whether a value retrieved by the outer query is not a part of  the results set of the values retrieved by the inner query.使用 EXISTS 操作查找至少有一个雇员的经理SELECT employee_id, last_name, job_id, department_idFROM   employees outerWHERE  EXISTS ( SELECT 'X'//如果返回X,则TRUE,否则FALSE.最后看是不是TRUE,即返回X                 FROM   employees的即为满足条件的.                 WHERE  manager_id =                         outer.employee_id);EMPLOYEE_ID LAST_NAME                 JOB_ID     DEPARTMENT_ID ----------- ------------------------- ---------- -------------         100 King                      AD_PRES               90        101 Kochhar                   AD_VP                 90        102 De Haan                   AD_VP                 90           103 Hunold                    IT_PROG               60          108 Greenberg                 FI_MGR               100         114 Raphaely                  PU_MAN                30        120 Weiss                     ST_MAN                50        121 Fripp                     ST_MAN                50        122 Kaufling                  ST_MAN                50        123 Vollman                   ST_MAN                50        124 Mourgos                   ST_MAN                50...//   解析一下::只要manager_id在employee_id中就显示,即是它的经理.SQL> select distinct manager_id  2  FROM   employees  3  WHERE  manager_id IS NOT NULL;MANAGER_ID----------       100       101       102       103       108       114       120       121       122       123       124       145       146       147       148       149       201       205SQL> select 'X' from dual;'-X   SELECT employee_id,last_name,job_id,department_id   FROM   employees    WHERE  employee_id IN (SELECT manager_id                          FROM   employees                          WHERE  manager_id IS NOT NULL);Using the EXISTS Operator    使用下面的条件，当至少找到一个经理号和雇员号相匹配的记录时，EXISTS 操作确保在内查询中的搜索不再继续：   WHERE manager_id = outer.employee_id.  Note that the inner SELECT query does not need to return a specific value(内查询不必找到确切的值), so a constant(常量也可以选择) can be selected. From a performance standpoint, it is faster to select a constant than a column.  Note: Having EMPLOYEE_ID in the SELECT clause of the inner query causes a table scan for that column. Replacing it with the literal X, or any constant, improves performance. This is more efficient than using the IN operator.   A IN construct can be used as an alternative for a EXISTS operator, as shown in the following example:    SELECT employee_id,last_name,job_id,department_id   FROM   employees    WHERE  employee_id IN (SELECT manager_id                          FROM   employees                          WHERE  manager_id IS NOT NULL);使用 NOT EXISTS 操作找出所有的没有任何雇员的部门SELECT department_id, department_nameFROM departments dWHERE NOT EXISTS (SELECT 'X'                  FROM   employees                  WHERE  department_id //要有一个联联                         = d.department_id);DEPARTMENT_ID DEPARTMENT_NAME------------- ------------------------------          120 Treasury          130 Corporate Tax          140 Control And Credit...可以得出以下是未选定行.SQL> select * from employees  2  where department_id=120;未选定行SQL> select * from employees  2  where department_id=130;未选定行...Using the NOT EXISTS Operator Alternative Solution   A NOT IN construct can be used as an alternative for a NOT EXISTS operator, as shown in the following example.    SELECT department_id, department_name   FROM   departments    WHERE  department_id NOT IN (SELECT department_id                                FROM   employees);   However, NOT IN evaluates to FALSE if any member of the set is a NULL value. 如果集合的任何成员是NULL值,NOT IN 的值是FALSE.因此,即使departments表中满足WHERE条件的行,你的查询将不会返回任何行.Therefore, your query will not return any rows even if there are rows in the departments table that satisfy the WHERE condition. 相关 UPDATEUPDATE table1 alias1SET    column = (SELECT expression                 FROM   table2 alias2                 WHERE  alias1.column =                            alias2.column);用一个相关子查询来更新在一个表中的行，该表基于另一个表中的行Correlated UPDATE   In the case of the UPDATE statement, you can use a correlated subquery to update rows in one table based on rows from another table.相关UPDATE:: 用一个附加的列来存储部门名称，反向规格化 EMPLOYEES 表:: 用相关更新填充表ALTER TABLE employeesADD(department_name VARCHAR2(30));UPDATE employees eSET    department_name =               (SELECT department_name        FROM   departments d       WHERE  e.department_id = d.department_id);//要有一个关联Correlated UPDATE (continued)   The example in the slide denormalizes the EMPLOYEES table by adding a column to store the department_name and then populates the table by using a correlated update.   Here is another example for a correlated update.Problem Statement  Use a correlated subquery to update rows in the EMPLOYEES table based on rows from the REWARDS table:././././././UPDATE employeesSET    salary = (SELECT employees.salary + rewards.pay_raise                 FROM   rewards                 WHERE  employee_id  =  employees.employee_id                 AND   payraise_date =                       (SELECT MAX(payraise_date)                        FROM   rewards                       WHERE  employee_id = employees.employee_id))WHERE  employees.employee_id IN    (SELECT employee_id         FROM   rewards);  This example uses the REWARDS table. The REWARDS table has the columns EMPLOYEE_ID, PAY_RAISE, and PAYRAISE_DATE. Every time an employee gets a pay raise, a record with the details of the employee ID, the amount of the pay raise, and the date of receipt of the pay raise is inserted into the REWARDS table. The REWARDS table can contain more than one record for an employee. The PAYRAISE _DATE column is used to identify the most recent pay raise received by an employee.  In the example, the SALARY column in the EMPLOYEES table is updated to reflect the latest pay raise received by the employee. This is done by adding the current salary of the employee with the corresponding pay raise from the REWARDS table. 相关 DELETEDELETE FROM table1 alias1 WHERE  column operator (SELECT expression  FROM   table2 alias2  WHERE  alias1.column = alias2.column);  用一个相关子查询删除表中的行，该表基于另一个表中的行Correlated DELETE  In the case of a DELETE statement, you can use a correlated subquery to delete only those rows that also exist in another table. If you decide that you will maintain only the last four job history records in the JOB_HISTORY table, then when an employee transfers to a fifth job, you delete the oldest JOB_HISTORY row by looking up the JOB_HISTORY table for the MIN(START_DATE)for the employee. The following code illustrates how the preceding operation can be performed using a correlated DELETE:   DELETE FROM job_history JH   WHERE  employee_id =         (SELECT employee_id           FROM   employees E          WHERE  JH.employee_id = E.employee_id././././要有一个关联          AND    start_date =                (SELECT MIN(start_date)                   FROM   job_history JH                 WHERE  JH.employee_id = E.employee_id)//关联                 AND 5 >  (SELECT COUNT(*)                             FROM   job_history JH                           WHERE  JH.employee_id = E.employee_id//关联                           GROUP  BY employee_id                           HAVING COUNT(*) >= 4));相关删除 DELETE   用一个相关子查询删除哪些在 EMPLOYEES 表和 EMP_HISTORY 表中的 employee_id 列值相同的行DELETE FROM employees EWHERE employee_id =             (SELECT employee_id            FROM   emp_history             WHERE  employee_id = E.employee_id);Correlated DELETE (continued)ExampleTwo tables are used in this example. They are:- The EMPLOYEES table, which gives details of all the current employees- The EMP_HISTORY table, which gives details of previous employees  EMP_HISTORY contains data regarding previous employees, so it would be erroneous if the same employee’s record existed in both the EMPLOYEES and EMP_HISTORY tables. You can delete such erroneous records by using the correlated subquery shown in the slide.WITH子句:: 当一个查询块在一个复杂的查询中出现多次时，使用 WITH 子句，能够在 SELECT 语句中多次使用相同查询块:: WITH 子句取回查询块的结果，并且将它存在用户的临时表空间中:: WITH 子句可以改善性能The WITH clause  Using the WITH clause, you can define a query block before using it in a query. The WITH clause (formally known as subquery_factoring_clause) enables you to reuse the same query block in a SELECT statement when it occurs more than once within a complex query. This is particularly useful when a query has many references to the same query block and there are joins and aggregations.   Using the WITH clause, you can reuse the same query when it is high cost to evaluate the query block and it occurs more than once within a complex query. Using the WITH clause, the Oracle Server retrieves the results of a query block and stores it in the user’s temporary tablespace. This can improve performance. WITH Clause Benefits- Makes the query easy to read- Evaluates a clause only once, even if it appears multiple times in the query,     thereby enhancing performance WITH 子句: 例子   用 WITH 子句，写一个查询来显示部门名称和该部门的合计薪水，哪些人的合计薪水高于各部门的平均薪水WITH dept_costs  AS (   SELECT  d.department_name, SUM(e.salary) AS dept_total   FROM    employees e, departments d   WHERE   e.department_id = d.department_id   GROUP BY d.department_name),avg_cost    AS (   SELECT SUM(dept_total)/COUNT(*) AS dept_avg   FROM   dept_costs)SELECT * FROM   dept_costs WHERE  dept_total >        (SELECT dept_avg          FROM avg_cost)ORDER BY department_name;  The  problem in the slide would require the following intermediate calculations:1.Calculate the total salary for every department, and store the result using a WITH clause.2.Calculate the average salary across departments, and store the result using a WITH clause.3.Compare the total salary calculated in the first step with the average salary calculated in the second step. If the total salary for a particular department is greater than the average salary across departments, display the department name and the total salary for that department. WITH Clause: Example (continued)   The SQL code in the slide is an example of a situation in which you can improve performance and write SQL more simply by using the WITH clause. The query creates the query names DEPT_COSTS and AVG_COST and then uses them in the body of the main query. Internally, the WITH clause is resolved either as an in-line view or a temporary table. The optimizer chooses the appropriate resolution depending on the cost or benefit of temporarily storing the results of the WITH clause.  Note: A subquery in the FROM clause of a SELECT statement is also called an in-line view.(内部视图) The output generated by the SQL code on the slide will be as follows:DEPARTMENT_NAME                DEPT_TOTAL------------------------------ ----------Sales                              304500Shipping                           156400The WITH Clause Usage Notes  -- It is used only with SELECT statements  -- A query name is visible to all WITH element query blocks (including their subquery blocks) defined after it and the main query block itself (including its subquery blocks).  -- When the query name is the same as an existing table name, the parser searches from the inside out, the query block name takes precedence over the table name.  -- The WITH clause can hold more than one query. Each query is then separated by a comma.SUMMARY在本课中, 您应该已经学会如何：:: 返回多于一列的多列子查询:: 多列比较可以成对或非成对地进行:: 一个多列子查询也能够被用于一个 SELECT 语句的 FROM 子句:: 分级子查询在 Oracle9i 中得到了增强Summary   You can use multiple-column subqueries to combine multiple WHERE conditions into a single WHERE clause. Column comparisons in a multiple-column subquery can be pairwise comparisons or non-pairwise comparisons.  You can use a subquery to define a table to be operated on by a containing query.   Oracle 9i enhances the the uses of scalar subqueries. Scalar subqueries can now be used in:   : Condition and expression part of DECODE  and CASE   : All clauses of SELECT except GROUP BY     : SET clause and WHERE clause of UPDATE statement小结:: 无论何时一个子查询必须对每一个侯选行返回不同的结果，这时，相关子查询是有用的:: EXISTS 操作是测试值的存在性的布尔操作:: 相关子查询能够用于 SELECT, UPDATE, and DELETE 语句:: 在 SELECT 语句中你能够通过 WITH 子句多次使用相同的查询块Summary (continued)   The Oracle Server performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement. Using the WITH clause, you can reuse the same query when it is costly to reevaluate the query block and it occurs more than once within a complex query.************分级取回数据**************目标 完成本课后, 您应当能够执行下列操作：:: 解释分级查询的概念:: 创建一个树型结构的报告:: 格式化分级数据:: 从树型结构中去除分支 Lesson Aim  In this lesson, you learn how to use hierarchical queries to create tree-structured reports.(树型结构报告) EMPLOYEES 表中的例子数据Sample Data from the EMPLOYEES Table   Using hierarchical queries, you can retrieve data based on a natural hierarchical relationship between rows in a table. A relational database does not store records in a hierarchical way关系型数据库不能以分等级的方式存储. However, where a hierarchical relationship exists between the rows of a single table, a process called tree walking enables the hierarchy to be constructed. A hierarchical query is a method of reporting, in order, the branches of a tree.   Imagine a family tree with the eldest members of the family found close to the base or trunk of the tree and the youngest members representing branches of the tree. Branches can have their own branches, and so on.    A hierarchical query is possible when a relationship exists between rows in a table.当在一个表中存在行行之间的关系,分等级查询是有可能的. For example, in the slide, you see that employees with the job IDs of AD_VP, ST_MAN, SA_MAN, and MK_MAN report directly to the president of the company. We know this because the MANAGER_ID column of these records contain the employee ID 100, which belongs to the president (AD_PRES).    Note: Hierarchical trees are used in various fields such as human genealogy (family trees), livestock (breeding purposes), corporate management (management hierarchies), manufacturing (product assembly), evolutionary research (species development), and scientific research.自然树结构Natural Tree Structure    The EMPLOYEES table has a tree structure representing the management reporting line. The hierarchy can be created by looking at the relationship between equivalent values in the EMPLOYEE_ID and MANAGER_ID columns. This relationship can be exploited by joining the table to itself(这些表自连接将可以使用.). The MANAGER_ID column contains the employee number of the employee’s manager.  The parent-child relationship of a tree structure enables you to control:- The direction in which the hierarchy is walked- The starting point inside the hierarchy  Note: The slide displays an inverted tree structure of the management hierarchy of the employees in the EMPLOYEES table.分等级查询SELECT [LEVEL], column, expr...FROM   table[WHERE condition(s)][START WITH condition(s)][CONNECT BY PRIOR condition(s)] ;WHERE条件:expr comparison_operator(比较运算符) exprKeywords and Clauses(子句和关键字)  Hierarchical queries can be identified by the presence of the CONNECT BY and START WITH clauses.  In the syntax:SELECTIs the standard SELECT clause. LEVEL For each row returned by a hierarchical query, the LEVEL 为每一行返回分等级查询,pseudocolumn(列) returns 1 for a root row, 2 for a child of a root,and so on./././././FROM tableSpecifies the table, view, or snapshot containing the columns. You can select from only one table.(只能是一个表)WHERERestricts the rows returned by the query without affecting other rows ofthe hierarchy. (返回没有影响其它分等级行的查询)        conditionIs a comparison with expressions.(比较表达式条件)START WITHSpecifies the root rows of the hierarchy (where to start). This clause is required for a tree hierarchical query.指定层次的根行(从哪开始),这个子句对于树型分类查询是必须的.CONNECT BYSpecifies the columns in which the relationship between parent and child指定父子关系的列PRIORrows exist. This clause is required for a hierarchical query.行存在.对于分等级查询是必须的. The SELECT statement cannot contain a join or query from a view that contains a join.SELECT操作不能包括连接,或从一个包含连接的视图中查询遍历树起点:: 指定必须满足的条件:: 接受有效的条件 START WITH column1=value使用EMPLOYEES表,从名字是Kochhar的雇员开始...START WITH last_name='Kochhar'Walking the Tree(遍历树)  The row or rows to be used as the root of the tree are determined by the START WITH clause(行,或行S将作为树根决定于START WITH子句). The START WITH clause can be used in conjunction with any valid condition.START WITH子句在任何有效的条件连接使用 Examples Using the EMPLOYEES table, start with King, the president of the company.... START WITH manager_id IS NULL Using the EMPLOYEES table, start with employee Kochhar. A START WITH condition can contain a subquery.... START WITH employee_id = (SELECT employee_id                                  FROM    employees                                WHERE   last_name = 'Kochhar')  If the START WITH clause is omitted(忽略), the tree walk is started with all of the rows in the table as root rows(树的遍历将从表中所有的行,作为根开始遍历). If a WHERE clause is used, the walk is started with all the rows that satisfy the WHERE condition. This no longer reflects a true hierarchy.如果WHERE子句使用了,遍历将从所有的行开始,并且满足WHERE条件.这将不再返回树的层次. Note: The clauses CONNECT BY PRIOR and START WITH are not ANSI SQL standard.  Instructor Note    You may wish to add that multiple hierarchical outputs are generated if more than one row satisfies the START WITH condition.遍历树(有方向的查询)CONNECT BY PRIOR column1=column2从顶向下遍历,用employees表   父列是employee_id,子列是manager_id...CONNECT BY PRIOR employee_id=manager_id方向从顶向下---->Column1=Parent Key      Column2=Child Key从底向上---->Column1=Child Key     Column2=Parent Key Walking the Tree (continued)   The direction of the query, whether it is from parent to child or from child to parent, is determined by the CONNECT BY PRIOR column placement. The PRIOR operator refers to the parent row(PRIOR操作涉及到父行). To find the children of a parent row, the Oracle Server evaluates the PRIOR expression for the parent row and the other expressions for each row in the table(为了找到父行中的子行,ORACLE SERVER将PRIOR作用于父行,并且其它的表达式操作将用于表中的其它行,行的条件为真则说明子行在父行中). Rows for which the condition is true are the children of the parent. The Oracle Server always selects children by evaluating the CONNECT BY condition with respect to a current parent row.ExamplesWalk from the top down(自顶向下) using the EMPLOYEES table. Define a hierarchical relationship in which the EMPLOYEE_ID value of the parent row is equal to the MANAGER_ID value of the child row.... CONNECT BY PRIOR employee_id = manager_id Walk from the bottom up using the EMPLOYEES table. ... CONNECT BY PRIOR manager_id = employee_id  The PRIOR operator does not necessarily need to be coded immediately following the CONNECT BY(PRIOR操作符并不是非要直接跟随在CONNECT BY后). Thus, the following CONNECT BY PRIOR clause gives the same result(与先前的例子有相同的结果) as the one in the preceding example. ... CONNECT BY employee_id = PRIOR manager_id  Note: The CONNECT BY clause cannot contain a subquery. ././././.不能包含子查询遍历树:从底向上SELECT employee_id, last_name, job_id, manager_idFROM   employeesSTART  WITH  employee_id = 101CONNECT BY PRIOR manager_id = employee_id ;//从底向上EMPLOYEE_ID LAST_NAME                 JOB_ID     MANAGER_ID----------- ------------------------- ---------- ----------        101 Kochhar                   AD_VP             100        100 King                      AD_PRESWalking the Tree: From the Bottom Up   The example in the slide displays a list of managers starting with the employee whose employee ID is 101.Example  In the following example, EMPLOYEE_ID values are evaluated for the parent row and MANAGER_ID, and SALARY values are evaluated for the child rows. The PRIOR operator applies only to the EMPLOYEE_ID value.... CONNECT BY PRIOR employee_id = manager_idAND salary > 15000;SQL>  SELECT employee_id, last_name, job_id, manager_id  2   FROM   employees  3   START  WITH  employee_id = 101  4   CONNECT BY PRIOR manager_id = employee_id  5  and salary>15000;EMPLOYEE_ID LAST_NAME                 JOB_ID     MANAGER_ID----------- ------------------------- ---------- ----------        101 Kochhar                   AD_VP             100        100 King                      AD_PRES  To qualify as a child row, a row must have a MANAGER_ID value equal to the EMPLOYEE_ID value of the parent row and must have a SALARY value greater than $15,000.为了取得子行,要有列MANAGER_ID的值=父行中employee_id的值../././Instructor Note:: In the context of the second paragraph, you may wish to include that additional conditions added to the CONNECT BY PRIOR clause potentially eliminated the whole of the branch(潜在的限制树支), hence the EMPLOYEE_ID AND SALARY are evaluated for the parent row to determine if it is to be part of the output.SQL> SELECT  last_name||' reports to '|| PRIOR   last_name "Walk Top Down"  2  FROM    employees  3  start with employee_id=101  4  connect by prior manager_id=employee_id;Walk Top Down--------------------------------//插一下Kochhar reports toKing reports to KochharSQL> SELECT  last_name||' reports to '|| last_name "Walk Top Down"  2   FROM    employees  3   start with employee_id=101  4   connect by prior manager_id=employee_id;Walk Top Down--------------------------------------------------------------Kochhar reports to KochharKing reports to King遍历树:  从顶向下SELECT  last_name||' reports to '|| PRIOR   last_name "Walk Top Down"FROM    employeesSTART   WITH last_name = 'King'CONNECT BY PRIOR employee_id = manager_id ;Walking the Tree: From the Top Down  Walking from the top down, display the names of the employees and their manager. Use employee King as the starting point. Print only one column.Walk Top Down--------------------------------King reports toKing reports toKochhar reports to KingGreenberg reports to KochharFaviet reports to GreenbergChen reports to GreenbergSciarra reports to GreenbergUrman reports to GreenbergPopp reports to GreenbergWhalen reports to KochharMavris reports to KochharSELECT  last_name||' reports to '|| last_name "Walk Top Down"FROM    employeesSTART   WITH last_name = 'King'CONNECT BY PRIOR employee_id = manager_id ;Walk Top Down------------------------------------------King reports to KingKing reports to KingKochhar reports to KochharGreenberg reports to GreenbergFaviet reports to FavietChen reports to ChenSciarra reports to SciarraUrman reports to UrmanPopp reports to PoppWhalen reports to WhalenMavris reports to Mavris...用LEVEL伪列将行分等级Ranking Rows with the LEVEL Pseudocolumn  You can explicitly show the rank or level of a row in the hierarchy by using the LEVEL pseudocolumn(伪列). This will make your report more readable(这将使你的报告更容易读).  The forks where one or more branches split away from a larger branch are called nodes, and the very end of a branch is called a leaf, or leaf node. The diagram in the slide shows the nodes of the inverted tree with their LEVEL values. For example, employee Higgens is a parent and a child, while employee Davies is a child and a leaf.  The LEVEL PseudocolumnValueLevel1A root node(根)2 A child of a root node(根的孩子)3 A child of a child, and so on(根的孩子的孩子...) Note: A root node is the highest node within an inverted tree. A child node is any nonroot node. A parent node is any node that has children. A leaf node is any node without children. The number of levels returned by a hierarchical query may be limited by available user memory. In the slide, King is the root or parent (LEVEL = 1). Kochhar, De Hann, Mourgos, Zlotkey, Hartstein, Higgens, and Hunold are children and also parents (LEVEL = 2). Whalen, Rajs, Davies, Matos, Vargas, Gietz, Ernst, Lorentz, Abel, Taylor, Grant, and Fay are children and leaves. (LEVEL = 3 and LEVEL = 4)用 LEVEL 和 LPAD 格式化分级报告  创建一个报告显示公司的管理层，从最高级别开始，缩进下面跟随的级别COLUMN org_chart FORMAT A18SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_')        AS org_chartFROM   employees START WITH last_name='King' CONNECT BY PRIOR employee_id=manager_id;Formatting Hierarchical Reports Using LEVEL The nodes in a tree are assigned level numbers from the root. Use the LPAD function in conjunction with the pseudocolumn LEVEL to display a hierarchical report as an indented tree.(交错树状) In the example on the slide:   : LPAD(char1,n [,char2]) returns char1, left-padded to length n with the sequence of characters in char2. The argument n is the total length of the return value as it is displayed on your terminal screen.   : LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_')defines the display format.   : char1 is the LAST_NAME , n the total length of the return value, is length of the LAST_NAME +(LEVEL*2)-2 ,and  char2  is '_'. In other words, this tells SQL to take the LAST_NAME and left-pad it with the '_' character till the length of the resultant string is equal to the value determined by LENGTH(last_name)+(LEVEL*2)-2.   For King, LEVEL = 1. Hence, (2 * 1) - 2 = 2 - 2 = 0. So King does not get padded with any '_' character and is displayed in column 1.  For Kochhar, LEVEL = 2. Hence, (2 * 2) - 2 = 4 - 2 = 2 . So Kochhar gets padded with 2 '_' characters and is displayed indented. The rest of the records in the EMPLOYEES table are displayed similarly. Formatting Hierarchical Reports Using LEVEL (continued)King__Kochhar____Greenberg______Faviet______Chen______Sciarra______Urman______Popp____Whalen____MavrisORG_CHART--------------------____Baer____Higgins______Gietz__De Haan____Hunold______Ernst______Austin______Pataballa______Lorentz修剪分支用 WHERE 子句 用 CONNECT BY 子句去除一个结点(node)叶子还要去除一个分支(node,叶子都不要了)Where last_name !='Higgins'CONNECT BY PRIORemployee_id=manager_idAND last_name !='Higgins'范围:小    范围:大Pruning Branches  You can use the WHERE and CONNECT BY clauses to prune the tree; that is, to control which nodes or rows are displayed(控制哪些节点或行S不被显示). The predicate you use acts as a Boolean condition.ExamplesStarting at the root, walk from the top down, and eliminate employee Higgins in the result, but process(保留) the child rows.     SELECT  department_id, employee_id,last_name, job_id, salary     FROM    employees     WHERE   last_name  != 'Higgins'     START   WITH manager_id IS NULL     CONNECT BY PRIOR employee_id = manager_id;  Starting at the root, walk from the top down, and eliminate employee Higgins and all child rows.(除去整个分支)     SELECT  department_id, employee_id,last_name, job_id, salary     FROM    employees     START   WITH manager_id IS NULL     CONNECT BY PRIOR employee_id = manager_id     AND     last_name != 'Higgins';