Chapter 7: Advanced Queries
214~280
■Set Operators:
using a set operator: The number of
columns and the column types returned by the queries must match, although the column names
may be different
・UNION ALL:Returns all the rows retrieved by the queries, including duplicate rows
・UNION：Returns all non-duplicate rows retrieved by the queries.
・INTERSECT：Returns rows that are retrieved by both queries.
・MINUS：Returns the remaining rows when the rows retrieved by the second query are subtracted from the rows retrieved by the first query.
■Translate() Functions
TRANSLATE(x, from_string, to_string)
eg:SQL> SELECT TRANSLATE(12345,54321,67890)
2 FROM dual;
TRANS
-----
09876
SQL>
■DECODE() Function
DECODE(value, search_value, result, default_value)
DECODE() is an old Oracle proprietary function, and therefore you should use CASE expressions instead if you are using Oracle Database 9i and above (you will learn about CASE in the next section). The DECODE() function is mentioned here because you may encounter it when using older Oracle databases.
■CASE Expression
①CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
ELSE default_result
END
②CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE default_result
END
■Hierarchical Queries
Syntax：
SELECT [LEVEL], column, expression, ...
FROM table
[WHERE where_clause]
[[START WITH start_condition]
[CONNECT BY PRIOR prior_condition]];
・LEVEL is a pseudo column that tells you how far into a tree you are.
・start_condition specifies where to start the hierarchical query.
・prior_condition specifies the relationship between the parent and child rows.You must specify a CONNECT BY PRIOR clause when writing a hierarchical query.
eg:
①Using the LEVEL Pseudo Column
SQL> l
1 select level,
2 lpad(' ',2*level-1)||first_name||' '||last_name as employee
3 from more_employees
4 start with last_name ='Jones'
5* connect by prior employee_id =manager_id
SQL> /
LEVEL EMPLOYEE
---------- -------------------------
1 Susan Jones
2 Jane Brown
3 Henry Heyson
2 John Grey
3 Jean Blue
②Formatting the Results from a Hierarchical Query
1 select level,
2 lpad(' ',2*level-1)||first_name||' '||last_name as employee
3 from more_employees
4 start with last_name like'%n%'
5* connect by prior employee_id =manager_id
SQL> /
LEVEL EMPLOYEE
---------- -------------------------
1 Ron Johnson
2 Fred Hobbs
2 Rob Green
1 Susan Jones
2 Jane Brown
3 Henry Heyson
2 John Grey
3 Jean Blue
1 Rob Green
1 Jane Brown
2 Henry Heyson
1 Henry Heyson
1 Keith Long
1 Doreen Penn
③Using a Subquery in a START WITH Clause
SQL> l
1 select level,
2 lpad(' ',2*level-1)||first_name||' '||last_name as employee
3 from more_employees
4 start with employee_id =(
5 select employee_id
6 from more_employees
7 where first_name='Kevin'
8 and last_name='Black'
9 )
10* connect by prior employee_id=manager_id
SQL> /
LEVEL EMPLOYEE
---------- -------------------------
1 Kevin Black
2 Keith Long
2 Frank Howard
2 Doreen Penn
④
1 select level,
2 lpad(' ',2*level-1)||first_name||' '||last_name as employee
3 from more_employees
4 start with first_name='Kevin'
5 and last_name='Black'
6* connect by prior employee_id=manager_id
SQL> /
LEVEL EMPLOYEE
---------- -------------------------
1 Kevin Black
2 Keith Long
2 Frank Howard
2 Doreen Penn
⑤Including Other Conditions in a Hierarchical Query
You can include other conditions in a hierarchical query using a WHERE clause.
SQL> l
1 select level,
2 lpad(' ',2*level-1)||first_name ||' '||last_name as employess,
3 salary
4 from more_employees
5 where salary <=50000
6 start with employee_id =1
7 connect by prior employee_id =manager_id
8*
SQL> /
LEVEL EMPLOYESS SALARY
---------- ------------------------------ ----------
3 Rob Green 40000
3 Jane Brown 45000
4 Henry Heyson 30000
3 John Grey 30000
4 Jean Blue 29000
3 Keith Long 50000
3 Frank Howard 45000
3 Doreen Penn 47000
已选择8行。
⑥Eliminating Nodes and Branches from a Hierarchical Query
You can eliminate a particular node from a query tree using a WHERE clause.
SQL> select level,
2 lpad(' ',2*level-1)||first_name||' '||last_name as employee
3 from more_employees
4 where last_name!='Johnson'
5 start with employee_id =1
6 connect by prior employee_id =manager_id;
LEVEL EMPLOYEE
---------- -------------------------
1 James Smith
3 Fred Hobbs
3 Rob Green
2 Susan Jones
3 Jane Brown
4 Henry Heyson
3 John Grey
4 Jean Blue
2 Kevin Black
3 Keith Long
3 Frank Howard
3 Doreen Penn
⑦Traversing Upward Through the Tree
You don’t have to traverse a tree downward from parents to children: you can start at a child andtraverse upward. You do this by switching child and parent columns in the CONNECT BY PRIOR clause.
1)
1 select level,lpad(' ',2*level-1)||first_name ||' '||last_name as employee
2 from more_employees
3 start with last_name='Blue'
4* connect by prior manager_id =employee_id
SQL> /
LEVEL EMPLOYEE
---------- -------------------------
1 Jean Blue
2 John Grey
3 Susan Jones
4 James Smith
2)
1 select level,lpad(' ',2*level-1)||first_name ||' '||last_name as employee
2 from more_employees
3 start with last_name='Blue'
4* connect by prior employee_id=manager_id
SQL> /
LEVEL EMPLOYEE
---------- -------------------------
1 Jean Blue
阅读(511) | 评论(0) | 转发(0) |
