SQL语句PART8

PairWise subquery:
e.g.:
select * from wf_docsort where (ndocsortid,nmoduleinfoid)  in (select ndocsortid, nmoduleinfoid from wf_docsort where instr(cname,'文')>0)
the above sql is the same function as:
select * from wf_docsort where ndocsortid = (select ndocsortid from wf_docsort where instr(cname,'xx')>0) and nmoduleinfoid = (select nmoduleinfoid from wf_docsort where instr(cname,'xx')>0)


A scalar subquery expression is a subquery that: returns exactly one column value from one row. Scalar subqueries can be used in: 1) The condition and expression part of DECODE and CASE 2) All clauses of SELECT except GROUP BY 3) The SET clause and WHERE clause of an UPDATE statement
e.g.:
select * from wf_docsort
results:
1    1    收文            SHOUWEN
2    1    发文            FAWEN
3    1    交办            JIAOBAN
4    2    值班报告     REPORT
5    3    督察督办     SUPERVISAL
6    2    值班快报     DAILYREPORT
7    2    我的事情     NOTIFY
8    4    提案            RESOLUTION
9    4    建议            SUGGESTION
select NDOCSORTID, NMODULEINFOID,CNAME,KEY,(
       case when nmoduleinfoid=(
               select nmoduleinfoid from wf_moduleinfo where nmoduleinfoid=1
        ) then 'JEAN1' else (case when nmoduleinfoid=(select nmoduleinfoid from wf_moduleinfo
                                        where nmoduleinfoid=2) then 'JEAN2' else 'JEAN3' END) END
) AS TITLE from wf_docsort;
results:
1    1    收文            SHOUWEN            JEAN1
2    1    发文            FAWEN                 JEAN1
3    1    交办            JIAOBAN               JEAN1
4    2    值班报告     REPORT                JEAN2
5    3    督察督办     SUPERVISAL         JEAN3
6    2    值班快报     DAILYREPORT       JEAN2
7    2    我的事情     NOTIFY                  JEAN2
8    4    提案            RESOLUTION         JEAN3
9    4    建议            SUGGESTION        JEAN3



A scalar subquery expression:
e.g.:
select * from t1
1    公文    GW
2    值班    DUTY
3    督察    SUPERVISAL
4    建议    SUGGESTION
select * from t2
1    1    收文            SHOUWEN
2    1    发文            FAWEN
3    1    交办            JIAOBAN
4    2    值班报告     REPORT
5    3    督察督办     SUPERVISAL
6    2    值班快报     DAILYREPORT
7    2    我的事情     NOTIFY
8    4    提案            RESOLUTION
9    4    建议            SUGGESTION
select * from t1 t1 order by (select t2.key from t2 t2 where t1.nmoduleinfoid = t2.nmoduleinfoid)
results:
4    2    值班报告     REPORT
6    2    值班快报     DAILYREPORT
7    2    我的事情     NOTIFY
1    1    收文            SHOUWEN
2    1    发文            FAWEN
3    1    交办            JIAOBAN
8    4    提案            RESOLUTION
9    4    建议            SUGGESTION
5    3    督察督办     SUPERVISAL
delete from t1 where nmoduleinfoid = 3;
commit;
select * from t1 t1 order by (select t2.key from t2 t2 where t1.nmoduleinfoid = t2.nmoduleinfoid)
results:
4    2    值班报告     REPORT
6    2    值班快报     DAILYREPORT
7    2    我的事情     NOTIFY
1    1    收文            SHOUWEN
2    1    发文            FAWEN
3    1    交办            JIAOBAN
8    4    提案            RESOLUTION
9    4    建议            SUGGESTION

Correlated Subqueries
SELECT column1, column2, ... FROM table1 outer WHERE column1 operator (SELECT column1, column2 FROM table2 WHERE expr1 = .expr2);
e.g.:
create table temp_t1(tid number, tname varchar(200), salary float, deptid number);
insert into temp_t1 values(100,'a1', 1000.00, 10);
insert into temp_t1 values(200,'a2',1400.00, 11);
insert into temp_t1 values(300,'a3',1200,10);
insert into temp_t1 values(400,'a4',1100,10);
insert into temp_t1 values(500,'a5',1800,11);
commit;
select * from temp_t1 outer where salary>(select avg(salary) from temp_t1 where deptid = outer.deptid);
results:
300    a3    1200    10
500    a5    1800    11
e.g.:
update temp_t1 set salary=1200 where tid=400;
commit;
select * from temp_t1 outer where salary>(select avg(salary) from temp_t1 where deptid = outer.deptid);
results:
300    a3    1200    10
400    a4    1200    10
500    a5    1800    11
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); // display those who had change their jobs at least twice.


Exists Operator
The EXISTS operator tests for existence of rows in the results set of the subquery. 1)  If a subquery row value is found: The search does not continue in the inner query, The condition is flagged TRUE 2)  If a subquery row value is not found: – The condition is flagged FALSE, The search continues in the inner query.
e.g.:
create table temp_t1(tid number, tname varchar(200), salary float, deptid number);
insert into temp_t1 values(100,'a1', 1000.00, 10);
insert into temp_t1 values(200,'a2',1400.00, 11);
insert into temp_t1 values(300,'a3',1200,10);
insert into temp_t1 values(400,'a4',1100,10);
insert into temp_t1 values(500,'a5',1800,11);
commit;
create table temp_t2(deptid number, deptname varchar2(200));
insert into temp_t2 values(10,'dept_a');
insert into temp_t2 values(12,'dept_c');
commit;
select * from temp_t1 t1 where exists (select * from temp_t2 where deptid=t1.deptid);
results:
100,'a1', 1000.00, 10;
300,'a3',1200,10;
400,'a4',1100,10;
select * from temp_t1 t1 where not exists (select * from temp_t2 where deptid=t1.deptid);
200,'a2',1400.00, 11;
500,'a5',1800,11

Correlated Update
UPDATE table1 alias1 SET column = (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column);
e.g.:
create table t3 as select * from wf_moduleinfo;
update t3 set key='mywords1' where nmoduleinfoid=1;
update t3 set key='mywords2' where nmoduleinfoid=2;
update t3 set key='mywords3' where nmoduleinfoid=3;
update t3 set key='mywords4' where nmoduleinfoid=4;
commits;
update t3 t3 set t3.key = (select t2.key from wf_moudleinfo t2 where t2.nmoduleinfoid=t3.nmoduleinfoid);
select * from t3;
result:
1    公文    GW
2    值班    DUTY
3    督察    SUPERVISAL
4    建议    SUGGESTION


Correlated Delete:
DELETE FROM table1 alias1 WHERE column operator (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column);
select * from t2
3    督察    SUPERVISAL
4    建议    SUGGESTION
select * from t1
1    1    收文            SHOUWEN
2    1    发文            FAWEN
3    1    交办            JIAOBAN
4    2    值班报告     REPORT
5    3    督察督办     SUPERVISAL
6    2    值班快报     DAILYREPORT
7    2    我的事情     NOTIFY
8    4    提案            RESOLUTION
9    4    建议            SUGGESTION
delete from t1 t1 where t1.nmoduleinfoid = (select t2.nmoduleinfoid from t2 t2 where t1.nmoduleinfoid=t2.nmoduleinfoid);
commit;
select * from t1
1    1    收文            SHOUWEN
2    1    发文            FAWEN
3    1    交办            JIAOBAN
4    2    值班报告     REPORT
6    2    值班快报     DAILYREPORT
7    2    我的事情     NOTIFY

With Clause:
Using the WITH clause, you can use the same query block in a SELECT statement when it occurs more than once within a complex query. 1) The WITH clause retrieves the results of a query block and stores it in the user’s temporary tablespace.2) The WITH clause may improve performance.
e.g.: target: Using the WITH clause, write a query to display the department name and total salaries for those departments whose total salary is greater than the average salary across departments.
WITH
dept_costs AS (
    SELECT d.department_name, SUM(e.salary) AS dept_total  FROM employees e JOIN departments d
    ON 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;


Using the Regular Expressions Functions and Conditions in SQL and PL/SQL
REGEXP_COUNT:  Returns the number of times a pattern match is found in an input sting
REGEXP_LIKE:      Similar to the LIKE operator, but performs regular expression matching instead of simple pattern                               matching (condition)
REGEXP_SUBSTR: Searches for a regular expression pattern within a given string and extracts the matched substring
REGEXP_INSTR:    Searches a string for a regular expression pattern and returns the position where the match is                                         found
REGEXP_REPLACE:  Searches for a regular expression pattern and replaces it with a replacement string
Using Metacharacters with regular Expressions
.    matches any character in the supported character set except null.
+   matches one or more occurrences
?   matches zero or one occurrence
*   matches zero or more occurrences of the preceding subexpression
{m}  matches exactly m occurrences of the preceding expression
{m, }   matches at least m occurrences of the preceding subexpression
{m,n}   matches at least m, but not more than n, occurrences of the preceding subexpression
[...]   matches any single character in the list within the brackets
|  matches one of the alternatives
(...)   treats the enclosed expression within the parentheses as a unit. the subexpression can be a string of literals or a complex expression containing operators.
^   matches the beginning of a string
$  matches the end of a string
/  treats the subsequent metacharacter in the expression as a literal
/n   matches the nth(1-9) preceding subexpression of whatever is grouped within parentheses. the parentheses cause an expression to be remembered; a backreference refers to it.
/d   a digit character
[:class:]   matches any character belonging to the specified POSIX character class.
[^:class:]:  matches any single character not in the list within the brackets.


Regular Expressions Functions and Conditions: Syntax
REGEXP_LIKE (source_char, parttern [, match_option]
REGEXP_INSTR (source_char, pattern [, position [, occurrence [, return_option [, match_option [, subexpr ]]]]])
REGEXP_SUBSTR (source_char, pattern [, position [, occurrence [, match_option [, subexpr]]]])
REGEXP_REPLACE (source_char, pattern [, replacestr [, position [, occurrence [, match_option ]]]])
REGEXP_COUNT (source_char, pattern [, position [, occurrence [, match_option ]]])
e.g.:
SELECT * from t1 where REGEXP_LIKE (key, 'N$') AND REGEXP_LIKE (key,'^S');
e.g.:
create table t1 as select * from wf_docsort;
select * from t1;
alter table t1 add telephone varchar2(200);
delete from t1 where nmoduleinfoid>1;
update t1 set telephone='0086.021.1233333';
commit;
SELECT ndocsortid, cname, key, REGEXP_REPLACE(telephone, '/.','-') AS phone FROM t1
1    收文    SHOUWEN   0086-021-1233333     
2    发文    FAWEN        0086-021-1233333
3    交办    JIAOBAN        0086-021-1233333
e.g.:
select * from t1;
1    收文    SHOUWEN   
2    发文    FAWEN      
3    交办    JIAOBAN    
update t1 set key='123abc' where ndocsortid=1;
commit;
SELECT CNAME, KEY,REGEXP_INSTR(KEY,'[[:alpha:]]') AS First_Alpha_Position FROM T1;
results:
收文    123abc       4  
发文    FAWEN      1
交办    JIAOBAN    1
e.g.:
SELECT REGEXP_SUBSTR(street_address , ' [^ ]+ ') AS Road FROM locations;
e.g.:
SELECT
REGEXP_INSTR
('0123456789', -- source char or search value
'(123)(4(56)(78))', -- regular expression patterns
1, -- position to start searching
1, -- occurrence
0, -- return option
'i', -- match option (case insensitive)
1) -- sub-expression on which to search
"Position"
FROM dual;
e.g.:
SELECT
REGEXP_SUBSTR
('acgctgcactgca', -- source char or search value
'acg(.*)gca', -- regular expression pattern
1, -- position to start searching
1, -- occurrence
'i', -- match option (case insensitive)
1) -- sub-expression
"Value"
FROM dual;
e.g.:
SELECT REGEXP_COUNT(
'ccacctttccctccactcctcacgttctcacctgtaaagcgtccctccctcatccccatgcccccttaccctgcag
ggtagagtaggctagaaaccagagagctccaagctccatctgtggagaggtgccatccttgggctgcagagagaggag
aatttgccccaaagctgcctgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagtt
ttcaccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagaggctcttgggtc
tgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggcatgtaggggcgtggggatgcgctctg
ctctgctctcctctcctgaacccctgaaccctctggctaccccagagcacttagagccag‘,
‘gtc’) AS Count
FROM dual;
e.g.:
alter table t1 add email varchar2(200) CHECK(REGEXP_LIKE(email,'@')) NOVALIDATE;
update t1 set email='1234' where ndocsortid=1;
commit;
error raised: ORA-02290