• 一、原题 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明显子查询的条件不符合题意，这里不会返回结果。


展开全文
• 对于有的更新语句，要更新的表可能条件不够，需要用到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提供了update的from子句，可以将要更新的表与其它的数据源连接起来。虽然只能对一个表进行更新，但是通过将要更新的表与其它的数据源连接起来，就可以在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)

有三点需要注意：
对于一个给定的a.keyfield的值，SELECT b.fieldsource FROM tableb b WHERE a.keyfield = b.keyfield 的值只能是一个唯一值，不能是多值。在绝大多数情况下，最后面的where EXISTS子句是重要的，否则将得到错误的结果。对于视图更新的限制： 如果视图基于多个表的连接，那么用户更新（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 子句以外的所有子句中
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 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
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   /./././

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
...
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
----------- ------------------------- ---------- -------------
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

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)]
[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操作不能包括连接,或从一个包含连接的视图中查询

遍历树

起点

:: 指定必须满足的条件
:: 接受有效的条件

使用EMPLOYEES表,从名字是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.

Examples

Using the EMPLOYEES table, start with King, the president of the company.

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
----------- ------------------------- ---------- ----------

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
----------- ------------------------- ---------- ----------

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
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
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)

创建一个报告显示公司的管理层，从最高级别开始，缩进下面跟随的级别

COLUMN org_chart FORMAT A18
AS org_chart
FROM   employees
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
子查询：用子查询能解决的问题假想你想要写一个查询来找出挣钱比陆涛的薪水还的人。为了解决这个问题，你需要两个查询：一 个找出陆涛的收入，第二个查询找出收入高于陆涛的人。你可以用组合两个查询的方法解决这...
• 子查询 一、单行和多行子查询1.单行子查询 单行子查询是指返回一行数据的子查询语句。当在where子句中引用单行子查询时，可以使用单行比较符(=,<,>,<=,>=,<>)。SELECT ename,sal,deptno FROM emp...
• 子查询结果返回一个值（单列单行） 选择比较运算符：> < >= <= = <> select ename from emp_xxx where salary > ( select salary from emp_xxx where ename = '张无忌' ) ; 子查询结果返回...
• 子查询在 SELECT、UPDATE、DELETE 语句内部可以出现 SELECT 语句。内部的 SELECT 语句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时表。子查询的类型有： 单行子查询：不向外部返回结果，或者...
• CMD命令行连接Oracle数据库 1、使用CMD命令行连接Oracle数据库应该跟连接MySQL数据库差不多，因此这里简单介绍下 2、命令：conn 用户名/密码@数据库所在IP:端口号/实例名 ⑴因为在Oracle中用户对应数据库，因此用...
• 为了从张表中查询，必须识别连接张表的公共。一般是在WHERE子句中用比较运算符指明连接的条件。   两个表连接有四种连接方式： * 相等连接 * 不等连接(看作单表查询) * 外连接 * 自连接(自关联)   1...
• ORACLE UPDATE 语句语法与性能分析的认识：  为了方便起见,建立了以下简单模型,和构造了部分测试数据:  在某个业务受理系统BSS中，  --客户资料表  create table customers ...
• ## OracleUpDate语法解析

千次阅读 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
• ## ORACLEUPDATE 语句语法

千次阅读 2008-03-06 16:49:00
ORACLE UPDATE 语句语法 为了方便起见,建立了以下简单模型,和构造了部分测试数据: 在某个业务受理系统BSS中， --客户资料表 create table customers ( customer_id number(8) not null, -- 客户标示 city...
• 表的内关联子查询子查询可以用主查询里面定义的表进行关联实现查询，这样的查询我们把它叫为表的内关联子查询 下图是一个表的内关联子查询的例子： 如图所示，我们在Where条件中写入子查询，然后再调用主查询定义...
• 前言  考虑到这样一种情况:查询员工中有哪些员工的工资比SMITH高。要解决这类问题我们首先要  要的就是将SIMTH的工资查询出来，之后再将所有员工的... 子查询其实就是指嵌入到其他语句中的select语句，也称其为
• 由于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 = ...
• 文章目录一、子查询作为条件二、子查询作为数据源三、子查询作为常量四、子查询进行增删改五、子查询分类1 语法2 注意 一、子查询作为条件 查询结果作为一个集合，跟在WHERE或HAVING字句中 1.查询SMITH所在部门的...
• 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 对于汉字，如果数据库字符编码为 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语句优化研究 一、 update语句的语法与原理 1. 语法 单表：UPDATE 表名称 SET 名称 = 新值 WHERE 名称 = 某值 如：update t_join_situation set join_state='1'...
• Oracle数据库中重子查询的使用代码如下:SELECT T.*, ROWNUM RN FROM (SELECT B.*, NVL(A.AMOUNT, 0) AMOUNT, V.FULLNAME, DECODE(SHARE_TYPE, 1, '***', 0, '%%%') SHARE_TYPE
• 子查询一般出现在SELECT语句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出现子查询子查询比主查询先执行，结果作为主查询的条件，在书写上要用圆括号扩起来，并放在比较运算符的右侧。子查询可以嵌套使用，...

...