精华内容
下载资源
问答
  • 一、原题 View the Exhibit and examine the description for the CUSTOMERS ...You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the
    一、原题
    View the Exhibit and examine the description for the CUSTOMERS table.

    You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566.
    Which UPDATE statement will accomplish the task?
    A.
    UPDATE customers
       SET cust_income_level = (SELECT cust_income_level
                                  FROM customers
                                 WHERE cust_id = 2560),
           cust_credit_limit = (SELECT cust_credit_limit
                                  FROM customers
                                 WHERE cust_id = 2566)
     WHERE cust_id = 2360;

    B.
    UPDATE customers
       SET (cust_income_level, cust_credit_limit) = (SELECT cust_income_level,
                                                            cust_credit_limit
                                                       FROM customers
                                                      WHERE cust_id = 2560
                                                         OR cust_id = 2566)
     WHERE cust_id = 2360;

    C.
    UPDATE customers
       SET (cust_income_level, cust_credit_limit) = (SELECT cust_income_level,
                                                            cust_credit_limit
                                                       FROM customers
                                                      WHERE cust_id IN
                                                            (2560, 2566))
     WHERE cust_id = 2360;

    D.
    UPDATE customers
       SET (cust_income_level, cust_credit_limit) = (SELECT cust_income_level,
                                                            cust_credit_limit
                                                       FROM customers
                                                      WHERE cust_id = 2560
                                                        AND cust_id = 2566)
     WHERE cust_id = 2360;

    答案:A

    二、题目翻译
    查看CUSTOMERS表的结构:
    要更新CUST_ID为2360的CUST_INCOME_LEVEL和CUST_CREDIT_LIMIT列值,要让CUST_INCOME_LEVEL的值与CUST_ID为2560的值一样,让CUST_CREDIT_LIMIT的值与CUST_ID为2566的值一样。
    哪一个UPDATE语句能完成该任务?

    三、题目解析
    B和C选项不正确,由于子查询里返回多行记录,而这里又是=,所以报错。
    D明显子查询的条件不符合题意,这里不会返回结果。

    展开全文
  • oracle update left join查询

    千次阅读 2018-11-16 23:23:23
    对于有的更新语句,要更新的表可能条件不够,需要用到left...update imim_gireqbillitems gi left join imim_gireqbills g on g.guid=gi.guid set gi.quantity= ( select sum(gi2.quantity) from imim_gireqbil...

    对于有的更新语句,要更新的表可能条件不够,需要用到left join关联其他表,

    但是不能直接关联,否则报错:错误如下:

    update  imim_gireqbillitems gi 
    left join imim_gireqbills g on g.guid=gi.guid
    set gi.quantity=
    (
        select sum(gi2.quantity)
        from imim_gireqbillitems gi2
        left join imim_gireqbills g2 on g2.guid=gi2.guid
        where g2.ismerge='0' and g2.mergeid=g.mergeid
    )
    where g.ismerge='1' and exists
    (
        select 1
        from imim_gireqbillitems gi2
        left join imim_gireqbills g2 on g2.guid=gi2.guid
        where g2.ismerge='0' and g2.mergeid=g.mergeid
    )
    

    正确实例:

    update 
    (
      select * from imim_gireqbillitems gi 
      left join imim_gireqbills g on g.guid=gi.guid
      where g.ismerge='1'
    ) T
    set T.quantity=
    (
        select sum(gi2.quantity)
        from imim_gireqbillitems gi2
        left join imim_gireqbills g2 on g2.guid=gi2.guid
        where g2.ismerge='0' and g2.mergeid=T.mergeid
    )
    where exists
    (
        select 1
        from imim_gireqbillitems gi2
        left join imim_gireqbills g2 on g2.guid=gi2.guid
        where g2.ismerge='0' and g2.mergeid=T.mergeid
    )
    
    

    Oracle中的 UPDATE FROM 解决方法

    在表的更新操作中,在很多情况下需要在表达式中引用要更新的表以外的数据。像sql server提供了updatefrom子句,可以将要更新的表与其它的数据源连接起来。虽然只能对一个表进行更新,但是通过将要更新的表与其它的数据源连接起来,就可以在update的表达式中引用要更新的表以外的其它数据。
    例如:

    UPDATE Table2
    SET Table2.ColB = Table2.ColB + Table1.ColB
    FROM Table2 
    INNER JOIN Table1 
    ON (Table2.ColA = Table1.ColA);
    

    实际更新的操作是在要更新的表上进行的,而不是在from子句所形成的新的结果集上进行的。

    Oracle没有update from语法,可以通过两种写法实现同样的功能:

    1:子查询UPDATE A SET A.NAME=(SELECT B.NAME FROM B WHERE B.ID=A.ID),本查询要根据具体情况看看是否变通成如下

    (1)单列

    UPDATE A
    SET A.NAME=(SELECT B.NAME FROM B WHERE B.ID=A.ID)
    WHERE A.ID IN (SELECT ID FROM B)

    (2)多列

    UPDATE order_rollup
    SET(qty,price)=(SELECT SUM(qty),SUM(price) FROM order_lines WHERE customer_id='KOHL' )
    WHERE cust_id='KOHL' AND order_period=TO_DATE('01-Oct-2000')
    

    2:利用视图来做

    UPDATE (SELECT A.NAME ANAME,B.NAME BNAME FROM A,B WHERE A.ID=B.ID)
    SET ANAME=BNAME;
    

    例如:

    UPDATE tablea a
    SET a.fieldforupdate = (SELECT b.fieldsource FROM tableb b WHERE a.keyfield = b.keyfield)
    WHERE EXISTS (SELECT b.fieldsource FROM tableb b WHERE a.keyfield = b.keyfield)
    

    有三点需要注意:

    1. 对于一个给定的a.keyfield的值,SELECT b.fieldsource FROM tableb b WHERE a.keyfield = b.keyfield 的值只能是一个唯一值,不能是多值。
    2. 在绝大多数情况下,最后面的where EXISTS子句是重要的,否则将得到错误的结果。
    3. 对于视图更新的限制:
      如果视图基于多个表的连接,那么用户更新(update)视图记录的能力将受到限制。除非update只涉及一个表且视图列中包含了被更新的表的整个主键,否则不能更新视图的基表。
    展开全文
  • oracle_高级子查询

    千次阅读 多人点赞 2014-11-12 15:09:20
     oracle_高级子查询子查询 子查询是嵌套在 SQL 语句中的另一...主查询与子查询返回的进行比较 where (column1,column2) in (子查询) 例:查询与141号或174号员工的manager_id和department_id相同的其
    

    oracle_高级子查询

    ①子查询
    子查询是嵌套在 SQL 语句中的另一个SELECT 语句
    子查询 (内查询) 在主查询执行之前执行
    主查询(外查询)使用子查询的结果

    ②多列子查询
    主查询与子查询返回的多个列进行比较

    where (column1,column2) in (子查询)

    例:查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id

    多列子查询中的比较分为两种:
    1)成对比较
    SELECT employee_id, manager_id, department_id
    FROM employees
    WHERE  (manager_id, department_id) IN
                          (SELECT manager_id, department_id
                           FROM   employees
                           WHERE  employee_id IN (141,174))
    AND employee_id NOT IN (141,174);

     
    2)不成对比较
    SELECT  employee_id, manager_id, department_id
    FROM    employees
    WHERE manager_id IN  (SELECT  manager_id
                       FROM    employees
                       WHERE   employee_id IN (174,141))
    AND  department_id IN  (SELECT  department_id
                       FROM    employees
                       WHERE   employee_id IN (174,141))
    AND employee_id NOT IN(174,141);

    ③在 FROM 子句中使用子查询

    例:返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资

    1)select last_name,department_id,salary,
    (select avg(salary)from employees e3 where e1.department_id = e3.department_id group by department_id) avg_salary
    from employees e1
    where salary >
             (select avg(salary)
              from employees e2 
              where e1.department_id = e2.department_id
              group by department_id
              )

    2)SELECT  a.last_name, a.salary, a.department_id, b.salavg
    FROM    employees a, (SELECT   department_id,
                          AVG(salary) salavg
                          FROM     employees
                          GROUP BY department_id) b
    WHERE   a.department_id = b.department_id
    AND     a.salary > b.salavg;

    ④单列子查询表达式
    单列子查询表达式是在一行中只返回一列的子查询
    Oracle8i 只在下列情况下可以使用, 例如:
    SELECT 语句 (FROM 和 WHERE 子句)
    INSERT 语句中的VALUES列表中
    Oracle9i中单列子查询表达式可在下列情况下使用:
    DECODE  和 CASE
    SELECT 中除 GROUP BY 子句以外的所有子句中

    1)在 CASE 表达式中使用单列子查询
    例:显式员工的employee_id,last_name和location。其中,
    若员工department_id与location_id为1800的department_id相同,则location为’Canada’,其余则为’USA’。

    SELECT employee_id, last_name,
           (CASE WHEN department_id = (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada'
          ELSE 'USA' END
     ) location
    FROM   employees;

    2)在 ORDER BY 子句中使用单列子查询
    例:查询员工的employee_id,last_name,要求按照员工的department_name排序
    SELECT   employee_id, last_name
    FROM     employees e
    ORDER BY (SELECT department_name
              FROM departments d
              WHERE e.department_id = d.department_id);

    ⑤相关子查询
     相关子查询按照一行接一行的顺序执行,主查询的每一行都执行一次子查询

     SELECT column1, column2, ...
     FROM   table1 outer
     WHERE  column1 operator (SELECT  colum1, column2
                              FROM    table2
                              WHERE   expr1 = outer.expr2);

    例:查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
    SELECT last_name, salary, department_id
    FROM   employees outer
    WHERE  salary >(SELECT AVG(salary)
       FROM   employees
       WHERE  department_id =  outer.department_id)

    例:若employees表中employee_id与job_history表中employee_id相同的数目不小于2,输出这些相同id的员工的employee_id,last_name和其job_id
    SELECT e.employee_id, last_name,e.job_id
    FROM   employees e
    WHERE  2 <= (SELECT COUNT(*)
                 FROM   job_history
                 WHERE  employee_id = e.employee_id);
    ⑥EXISTS 操作符
    EXISTS 操作符检查在子查询中是否存在满足条件的行
    1.如果在子查询中存在满足条件的行:
    不在子查询中继续查找
    条件返回 TRUE
    2.如果在子查询中不存在满足条件的行:
    条件返回 FALSE
    继续在子查询中查找

    例:查询公司管理者的employee_id,last_name,job_id,department_id信息
    SELECT employee_id, last_name, job_id, department_id
    FROM   employees outer
    WHERE  EXISTS ( SELECT 'X'
                     FROM   employees
                     WHERE  manager_id =
                            outer.employee_id);

    例:查询departments表中,不存在于employees表中的部门的department_id和department_name
    SELECT department_id, department_name
    FROM departments d
    WHERE NOT EXISTS (SELECT 'X'
                      FROM   employees
                      WHERE  department_id
                             = d.department_id);

    ⑦相关更新
    UPDATE table1 alias1
    SET    column = (SELECT expression
                     FROM   table2 alias2
                     WHERE  alias1.column =   
                            alias2.column);
    使用相关子查询依据一个表中的数据更新另一个表的数据

    ⑧相关删除
    DELETE FROM table1 alias1
     WHERE  column operator
     (SELECT expression
       FROM   table2 alias2
       WHERE  alias1.column = alias2.column);
    ⑨WITH 子句
    使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块
    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;

    展开全文
  • ORACLE 高级子查询

    千次阅读 2014-07-30 22:16:43
    :: 在返回空值时描述并解释子查询的行为 :: 写一个在 FROM 子句中的子查询 :: SQL 中使用分级子查询 :: 描述能够用相关子查询解决的问题类型 :: 写相关子查询 :: 用相关子查询更新和删除行 :: 使用 EXISTS 和 NOT ...
    :: 写多列子查询
    :: 在返回空值时描述并解释子查询的行为
    :: 写一个在 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.
    先执行内查询,返回值给外查询,再执行主查询.
    
    
    
    
    				子查询
    
    SELECT	select_list
    FROM	table
    WHERE	expr 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:			././././.
    	operator	includes 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_name
    FROM   employees         10500
    WHERE  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		       ||   |
    		100	90		_______||   |
    		102	60		________|   |
    		124	50		____________|
    
    
    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子句.)
    
      Syntax	
    	SELECT column,column, ...
    	FROM table
    	WHERE (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 或 174
    SELECT	employee_id, manager_id, department_id
    FROM	employees
    WHERE  (manager_id, department_id) IN
                          (SELECT manager_id, department_id
                           FROM   employees
                           WHERE  employee_id IN (178,174))
    AND	employee_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
           149
    
    SQL> 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            80
    
    SQL> 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    employees
    WHERE   manager_id IN 
                      (SELECT  manager_id		1/
                       FROM    employees
                       WHERE   employee_id IN (174,141))	
    AND     department_id IN 
                      (SELECT  department_id	2/
                       FROM    employees
                       WHERE   employee_id IN (174,141))
    AND	employee_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_ID		MANAGER_ID		DEPARTMENT_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) b
    WHERE   a.department_id = b.department_id
    AND     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,
           (CASE			20 <----|  
            WHEN department_id =		|
                    (SELECT deaprtment_id FROM deaprtments
                     WHERE location_id=1800)
            THEN 'Canada' ELSE 'USA' END) location
    FROM   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_name
    FROM     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,则返回USA
    
    The result of the preceding example follows:
      EMPLOYEE_ID	LAST_NAME	LOCATI
       	  100	King		USA
    	  101	Kochhar		USA
    	  102	De Haan		USA
    	  103 	Hunold		USA
    ...
    	  201	Hartstein	Canada
     	  202	Fay		Canada
    	  206	Higgins		USA
    	  206	Gietz 		USA
    
    
    Scalar 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 row
    
    Correlated 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 outer
    WHERE 	column1 operator
    		      ( SELECT	column1,column2
    			FROM	table2
    			WHERE	expr1=			//要有一个关联
    				   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_id
    FROM   employees outer
    WHERE  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_id
    FROM   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_ID
    
    	3. 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,如果没有值返回,则返回FALSE
    Accordingly, 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_id
    FROM   employees outer
    WHERE  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
           205
    
    
    SQL> 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_name
    FROM departments d
    WHERE 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. 
    
    
    		相关 UPDATE
    
    UPDATE table1 alias1
    SET    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 employees
    ADD(department_name VARCHAR2(30));
    
    UPDATE employees e
    SET    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 employees
    SET    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. 
    
    
    
    			相关 DELETE
    
    DELETE 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 E
    WHERE employee_id =  
               (SELECT employee_id
                FROM   emp_history 
                WHERE  employee_id = E.employee_id);
    
    Correlated DELETE (continued)
    
    Example
    Two 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                              304500
    Shipping                           156400
    
    
    The 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(比较运算符) expr
    
    
    Keywords and Clauses(子句和关键字)
    
      Hierarchical queries can be identified by the presence of the CONNECT BY and START WITH clauses.
    
      In the syntax:
    
    	SELECT		Is 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 table	Specifies the table, view, or snapshot containing the columns. 					You can select from only one table.(只能是一个表)
    
    	WHERE		Restricts the rows returned by the query without affecting other 				rows of	the hierarchy. (返回没有影响其它分等级行的查询)
    
            condition	Is a comparison with expressions.(比较表达式条件)
    
    	START WITH	Specifies the root rows of the hierarchy (where to start). This 				clause is required for a tree hierarchical query.
    			指定层次的根行(从哪开始),这个子句对于树型分类查询是必须的.
    
    	CONNECT BY	Specifies the columns in which the relationship between parent 					and child
    			指定父子关系的列
    
    	PRIOR		rows 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.
    
    Examples
    
    Walk 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_id
    FROM   employees
    START  WITH  employee_id = 101
    CONNECT BY PRIOR manager_id = employee_id ;	//从底向上
    
    EMPLOYEE_ID LAST_NAME                 JOB_ID     MANAGER_ID
    ----------- ------------------------- ---------- ----------
            101 Kochhar                   AD_VP             100
            100 King                      AD_PRES			
    
    
    Walking 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_id
    					AND 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 to
    King reports to Kochhar
    
    SQL> 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 Kochhar
    King reports to King
    
    
    
    		遍历树:  从顶向下
    
    SELECT  last_name||' reports to '|| PRIOR   last_name "Walk Top Down"
    FROM    employees
    START   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 to
    King reports to
    Kochhar reports to King
    Greenberg reports to Kochhar
    Faviet reports to Greenberg
    Chen reports to Greenberg
    Sciarra reports to Greenberg
    Urman reports to Greenberg
    Popp reports to Greenberg
    Whalen reports to Kochhar
    Mavris reports to Kochhar
    
    SELECT  last_name||' reports to '|| last_name "Walk Top Down"
    FROM    employees
    START   WITH last_name = 'King'
    CONNECT BY PRIOR employee_id = manager_id ;
    
    Walk Top Down
    ------------------------------------------
    King reports to King
    King reports to King
    Kochhar reports to Kochhar
    Greenberg reports to Greenberg
    Faviet reports to Faviet
    Chen reports to Chen
    Sciarra reports to Sciarra
    Urman reports to Urman
    Popp reports to Popp
    Whalen reports to Whalen
    Mavris 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 Pseudocolumn
    
    Value	Level
    1	A 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 A18
    SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') 
           AS org_chart
    FROM   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
    ____Mavris
    
    ORG_CHART
    --------------------
    ____Baer
    ____Higgins
    ______Gietz
    __De Haan
    ____Hunold
    ______Ernst
    ______Austin
    ______Pataballa
    ______Lorentz
    
    
    				修剪分支
    
    	用 WHERE 子句 				用 CONNECT BY 子句
    	去除一个结点(node)叶子还要		去除一个分支(node,叶子都不要了)
    	
    Where last_name !='Higgins'		CONNECT BY PRIOR
    					employee_id=manager_id
    					AND 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.
    
    Examples
    
    Starting 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.
    先执行内查询,返回值给外查询,再执行主查询.
    
    
    
    
    				子查询
    
    SELECT	select_list
    FROM	table
    WHERE	expr 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:			././././.
    	operator	includes 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_name
    FROM   employees         10500
    WHERE  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		       ||   |
    		100	90		_______||   |
    		102	60		________|   |
    		124	50		____________|
    
    
    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子句.)
    
      Syntax	
    	SELECT column,column, ...
    	FROM table
    	WHERE (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 或 174
    SELECT	employee_id, manager_id, department_id
    FROM	employees
    WHERE  (manager_id, department_id) IN
                          (SELECT manager_id, department_id
                           FROM   employees
                           WHERE  employee_id IN (178,174))
    AND	employee_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
           149
    
    SQL> 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            80
    
    SQL> 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    employees
    WHERE   manager_id IN 
                      (SELECT  manager_id		1/
                       FROM    employees
                       WHERE   employee_id IN (174,141))	
    AND     department_id IN 
                      (SELECT  department_id	2/
                       FROM    employees
                       WHERE   employee_id IN (174,141))
    AND	employee_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_ID		MANAGER_ID		DEPARTMENT_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) b
    WHERE   a.department_id = b.department_id
    AND     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,
           (CASE			20 <----|  
            WHEN department_id =		|
                    (SELECT deaprtment_id FROM deaprtments
                     WHERE location_id=1800)
            THEN 'Canada' ELSE 'USA' END) location
    FROM   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_name
    FROM     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,则返回USA
    
    The result of the preceding example follows:
      EMPLOYEE_ID	LAST_NAME	LOCATI
       	  100	King		USA
    	  101	Kochhar		USA
    	  102	De Haan		USA
    	  103 	Hunold		USA
    ...
    	  201	Hartstein	Canada
     	  202	Fay		Canada
    	  206	Higgins		USA
    	  206	Gietz 		USA
    
    
    Scalar 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 row
    
    Correlated 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 outer
    WHERE 	column1 operator
    		      ( SELECT	column1,column2
    			FROM	table2
    			WHERE	expr1=			//要有一个关联
    				   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_id
    FROM   employees outer
    WHERE  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_id
    FROM   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_ID
    
    	3. 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,如果没有值返回,则返回FALSE
    Accordingly, 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_id
    FROM   employees outer
    WHERE  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
           205
    
    
    SQL> 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_name
    FROM departments d
    WHERE 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. 
    
    
    		相关 UPDATE
    
    UPDATE table1 alias1
    SET    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 employees
    ADD(department_name VARCHAR2(30));
    
    UPDATE employees e
    SET    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 employees
    SET    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. 
    
    
    
    			相关 DELETE
    
    DELETE 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 E
    WHERE employee_id =  
               (SELECT employee_id
                FROM   emp_history 
                WHERE  employee_id = E.employee_id);
    
    Correlated DELETE (continued)
    
    Example
    Two 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                              304500
    Shipping                           156400
    
    
    The 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(比较运算符) expr
    
    
    Keywords and Clauses(子句和关键字)
    
      Hierarchical queries can be identified by the presence of the CONNECT BY and START WITH clauses.
    
      In the syntax:
    
    	SELECT		Is 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 table	Specifies the table, view, or snapshot containing the columns. 					You can select from only one table.(只能是一个表)
    
    	WHERE		Restricts the rows returned by the query without affecting other 				rows of	the hierarchy. (返回没有影响其它分等级行的查询)
    
            condition	Is a comparison with expressions.(比较表达式条件)
    
    	START WITH	Specifies the root rows of the hierarchy (where to start). This 				clause is required for a tree hierarchical query.
    			指定层次的根行(从哪开始),这个子句对于树型分类查询是必须的.
    
    	CONNECT BY	Specifies the columns in which the relationship between parent 					and child
    			指定父子关系的列
    
    	PRIOR		rows 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.
    
    Examples
    
    Walk 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_id
    FROM   employees
    START  WITH  employee_id = 101
    CONNECT BY PRIOR manager_id = employee_id ;	//从底向上
    
    EMPLOYEE_ID LAST_NAME                 JOB_ID     MANAGER_ID
    ----------- ------------------------- ---------- ----------
            101 Kochhar                   AD_VP             100
            100 King                      AD_PRES			
    
    
    Walking 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_id
    					AND 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 to
    King reports to Kochhar
    
    SQL> 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 Kochhar
    King reports to King
    
    
    
    		遍历树:  从顶向下
    
    SELECT  last_name||' reports to '|| PRIOR   last_name "Walk Top Down"
    FROM    employees
    START   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 to
    King reports to
    Kochhar reports to King
    Greenberg reports to Kochhar
    Faviet reports to Greenberg
    Chen reports to Greenberg
    Sciarra reports to Greenberg
    Urman reports to Greenberg
    Popp reports to Greenberg
    Whalen reports to Kochhar
    Mavris reports to Kochhar
    
    SELECT  last_name||' reports to '|| last_name "Walk Top Down"
    FROM    employees
    START   WITH last_name = 'King'
    CONNECT BY PRIOR employee_id = manager_id ;
    
    Walk Top Down
    ------------------------------------------
    King reports to King
    King reports to King
    Kochhar reports to Kochhar
    Greenberg reports to Greenberg
    Faviet reports to Faviet
    Chen reports to Chen
    Sciarra reports to Sciarra
    Urman reports to Urman
    Popp reports to Popp
    Whalen reports to Whalen
    Mavris 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 Pseudocolumn
    
    Value	Level
    1	A 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 A18
    SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') 
           AS org_chart
    FROM   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
    ____Mavris
    
    ORG_CHART
    --------------------
    ____Baer
    ____Higgins
    ______Gietz
    __De Haan
    ____Hunold
    ______Ernst
    ______Austin
    ______Pataballa
    ______Lorentz
    
    
    				修剪分支
    
    	用 WHERE 子句 				用 CONNECT BY 子句
    	去除一个结点(node)叶子还要		去除一个分支(node,叶子都不要了)
    	
    Where last_name !='Higgins'		CONNECT BY PRIOR
    					employee_id=manager_id
    					AND 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.
    
    Examples
    
    Starting 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';
    展开全文
  • Oracle子查询

    千次阅读 2019-01-23 19:59:56
    1.子查询 语法: select 字段列表 from table where 表达式 operator (select 字段列表 from table); 说明:operator 是比较运算符(&gt;,&gt;=,&lt;,&lt;=,=,&lt;&gt;) 被()包裹的查询语句...
  • Oracle子查询

    千次阅读 2008-11-28 10:07:00
    子查询:用子查询能解决的问题假想你想要写一个查询来找出挣钱比陆涛的薪水还的人。为了解决这个问题,你需要两个查询:一 个找出陆涛的收入,第二个查询找出收入高于陆涛的人。你可以用组合两个查询的方法解决这...
  • Oracle子查询大全

    2019-05-05 10:55:44
    子查询 一、单行和多行子查询1.单行子查询 单行子查询是指返回一行数据的子查询语句。当在where子句中引用单行子查询时,可以使用单行比较符(=,<,>,<=,>=,<>)。SELECT ename,sal,deptno FROM emp...
  • 子查询结果返回一个值(单列单行) 选择比较运算符:> < >= <= = <> select ename from emp_xxx where salary > ( select salary from emp_xxx where ename = '张无忌' ) ; 子查询结果返回...
  • Oracle_子查询和常用函数

    千次阅读 2018-09-06 10:38:45
    子查询在 SELECT、UPDATE、DELETE 语句内部可以出现 SELECT 语句。内部的 SELECT 语句结果可以作为外部语句中条件子句的一部分,也可以作为外部查询的临时表。子查询的类型有: 单行子查询:不向外部返回结果,或者...
  • Oracle子查询、排序

    2020-10-07 22:58:36
    CMD命令行连接Oracle数据库 1、使用CMD命令行连接Oracle数据库应该跟连接MySQL数据库差不多,因此这里简单介绍下 2、命令:conn 用户名/密码@数据库所在IP:端口号/实例名 ⑴因为在Oracle中用户对应数据库,因此用...
  • Oracle 表查询,子查询

    千次阅读 2011-10-21 13:46:23
    为了从张表中查询,必须识别连接张表的公共。一般是在WHERE子句中用比较运算符指明连接的条件。   两个表连接有四种连接方式: * 相等连接 * 不等连接(看作单表查询) * 外连接 * 自连接(自关联)   1...
  • oracle update 张表的语法

    千次阅读 2010-11-18 12:15:00
    ORACLE UPDATE 语句语法与性能分析的认识:  为了方便起见,建立了以下简单模型,和构造了部分测试数据:  在某个业务受理系统BSS中,  --客户资料表  create table customers ...
  • Oracle UpDate语法解析

    千次阅读 2016-06-24 10:27:11
    一、利用子查询update A SET 字段1 =(select 字段表达式 from B WHERE ...), 字段2 =(select 字段表达式 from B WHERE ...) WHERE 逻辑表达式 UPDATE多个字段两种写法: 写法一: UPDATE table_1 a ...
  • oracle中的相关子查询(correlated subqueries) 以前我们曾介绍过oracle中的嵌套子查询,你可以在你需要的任何位置(除了group by子句)使用嵌套之查询,例如常用的where子句位置,你也可以在hav
  • ORACLE UPDATE 语句语法

    千次阅读 2008-03-06 16:49:00
    ORACLE UPDATE 语句语法 为了方便起见,建立了以下简单模型,和构造了部分测试数据: 在某个业务受理系统BSS中, --客户资料表 create table customers ( customer_id number(8) not null, -- 客户标示 city...
  • ORACLE SQL 表的内关联子查询

    千次阅读 2019-04-08 09:54:37
    表的内关联子查询子查询可以用主查询里面定义的表进行关联实现查询,这样的查询我们把它叫为表的内关联子查询 下图是一个表的内关联子查询的例子: 如图所示,我们在Where条件中写入子查询,然后再调用主查询定义...
  • Oracle数据库之SQL子查询详解

    千次阅读 2013-04-11 12:33:16
    前言  考虑到这样一种情况:查询员工中有哪些员工的工资比SMITH高。要解决这类问题我们首先要  要的就是将SIMTH的工资查询出来,之后再将所有员工的... 子查询其实就是指嵌入到其他语句中的select语句,也称其为
  • [Oracle] 表关联的update和delete

    万次阅读 2013-10-15 22:49:44
    由于Oracle不支持update或delete from语句,因此,Oracle表关联update和delete必须借助于子查询,同理,Oracle也不支持同时update或delete张表,其典型用法如下: 表关联update 首先,构造测试表和数据如下...
  • 为了从张表中查询,必须识别连接张表的公共。一般是在WHERE子句中用比较运算符指明连接的条件。   两个表连接有四种连接方式: * 相等连接 * 不等连接(看作单表查询) * 外连接 * 自连接(自关联)  ...
  • 1、利用子查询update A SET 字段1=(select 字段表达式 from B WHERE …), 字段2=(select 字段表达式 from B WHERE …) WHERE 逻辑表达式 UPDATE多个字段两种写法: 写法一: UPDATE table_1 a SET col_x1 = ...
  • ORACLE数据库(四)----子查询

    千次阅读 2021-03-15 21:23:12
    文章目录一、子查询作为条件二、子查询作为数据源三、子查询作为常量四、子查询进行增删改五、子查询分类1 语法2 注意 一、子查询作为条件 查询结果作为一个集合,跟在WHERE或HAVING字句中 1.查询SMITH所在部门的...
  • Oracle表关联更新字段(update

    万次阅读 2019-11-13 09:21:25
    Oracle可以用以下两种方式实现表关联更新字段: update ( select t1.name name1, t2.name name2 from table1 t1 left join table2 t2 on t1.id = t2.id where t1.age > 20 ) tmp set tmp.name1 = tmp.name2...
  • Oracle 子查询等常见的sql语句

    千次阅读 2017-03-27 11:00:59
    oracle 对于汉字,如果数据库字符编码为 GBK 占用2个字节,如果是UTF-8则占用3个字节。 1.建表 create table student(); create table class(id number primary key,name varchar(20)); 2.添加字段 alter ...
  • 我遇到此错误是在表关联updateUPDATE EDASYS.CELL_COMPONENT_T A SET A.ARRAY_GLASS_ID = (SELECT M.ARRAY_GLASS_ID FROM EDASYS.CELL_ARRAY_CF_MAPPING_T M WHERE M.CF_GLASS_ID = A.COMPONENT_ID AND ...
  • Oracle update 两表及以上关联更新,出现值情况,不是一对一更新
  • 为了从张表中查询,必须识别连接张表的公共。一般是在WHERE子句中用比较运算符指明连接的条件。   两个表连接有四种连接方式: * 相等连接 * 不等连接(看作单表查询) * 外连接 * 自连接(自关联)   1.
  • oracleupdate语句优化

    千次阅读 2014-09-12 12:04:01
    Oracleupdate语句优化研究 一、 update语句的语法与原理 1. 语法 单表:UPDATE 表名称 SET 名称 = 新值 WHERE 名称 = 某值 如:update t_join_situation set join_state='1'...
  • Oracle数据库重子查询嵌套的坑

    千次阅读 2017-05-25 08:57:24
    Oracle数据库中重子查询的使用代码如下:SELECT T.*, ROWNUM RN FROM (SELECT B.*, NVL(A.AMOUNT, 0) AMOUNT, V.FULLNAME, DECODE(SHARE_TYPE, 1, '***', 0, '%%%') SHARE_TYPE
  • Oracle之select查询语句

    千次阅读 2017-06-04 13:19:53
    子查询一般出现在SELECT语句的WHERE子句中,Oracle也支持在FROM或HAVING子句中出现子查询子查询比主查询先执行,结果作为主查询的条件,在书写上要用圆括号扩起来,并放在比较运算符的右侧。子查询可以嵌套使用,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 45,881
精华内容 18,352
关键字:

oracleupdate子查询多列