ORACLE 运算

来源：互联网发布：盘古网络永清县种养殖编辑：程序博客网时间：2024/05/29 14:47
集合运算
集合运算
集合运算组合两个或多个部分查询的结果到一个结果中。包含集合运算的查询称为复合查询。
Sql代码  
Operator        Returns  
  
UNION(联合)       由每个查询选择的所有不同的行(无重复值)  
UNION ALL       由每个查询选择的所有的行，包括所有重复的行  
INTERSECT(交叉)       由两个查询选择的所有不同的行  
MINUS           由第一个查询选择的所有不同的行  
  
  
   所有的集合运算与等号的优先级相同，如果SQL语句包含多个集合运算并且没有圆括号明确地指定另一个顺序，Oracle服务器将以从左到右的顺序计算。你应该使用圆括号来明确地指定带另外的集合运算的INTERSECT (相交) 运算查询中的赋值顺序。  
  
    注：在幻灯片中，图中的亮色代表查询结果。  
  
    INTERSECT (相交) 和MINUS (相减) 运算不是ANSI SQL-99兼容的，他们是Oracle特定的。  
  
  
在下面要用到的表：  
:: EMPLOYEES: 提供所有在职雇员当前的详细资料  
:: JOB_HISTORY:当一个雇员改变工作时，记录他的以前的工作的开始日期和结束日期、  
   departmentID和job ID的详细资料  
  
SQL> desc employees;  
 名称                                      是否为空? 类型  
 ----------------------------------------- -------- -------------  
 EMPLOYEE_ID                               NOT NULL NUMBER(6)  
 FIRST_NAME                                         VARCHAR2(20)  
 LAST_NAME                                 NOT NULL VARCHAR2(25)  
 EMAIL                                     NOT NULL VARCHAR2(25)  
 PHONE_NUMBER                                       VARCHAR2(20)  
 HIRE_DATE                                 NOT NULL DATE  
 JOB_ID                                    NOT NULL VARCHAR2(10)  
 SALARY                                             NUMBER(8,2)  
 COMMISSION_PCT                                     NUMBER(2,2)  
 MANAGER_ID                                         NUMBER(6)  
 DEPARTMENT_ID                                      NUMBER(4)  
  
  
QL> desc job_history  
名称                   是否为空?    类型  
---------------------- -------- -------------  
EMPLOYEE_ID            NOT NULL NUMBER(6)  
START_DATE             NOT NULL DATE  
END_DATE               NOT NULL DATE  
JOB_ID                 NOT NULL VARCHAR2(10)  
DEPARTMENT_ID                   NUMBER(4)  
  
  
  
[b]联合运算[/b]  
  
联合(UNION) 运算从两个查询返回除去重复值后的结果  
  
UNION(联合)运算  
UNION运算返回所有由任一查询选择的行。用UNION运算从多表返回所有行，但除去任何重复的行。  
  
原则./././././  
:: 被选择的列数和列的数据类型必须是与所有用在查询中的SELECT语句一致。列的名字不必相同。  
:: 联合运算在所有被选择的列上进行。  
:: 在做重复检查的时候不忽略空(NULL)值。././././  
:: IN运算有比UNION运算高的优先级。  
:: 在默认情况下，输出以SELECT子句的第一列的升序排序。./././././  
  
  
  
[b]使用联合集合运算[/b]  
      
   显示当前和以前所有雇员的工作岗位。每个雇员仅显示一次  
  
SELECT employee_id, job_id  
FROM employees  
UNION  
SELECT employee_id, job_id  
FROM job_history;  
  
使用联合集合(UNION SET)运算  
       联合运算消除重复记录，如果有相同的记录同时出现在EMPLOYEES和JOB_HISTORY表中，该记录只显示一次，观察幻灯片的输出显示雇员号为200的雇员的雇员号虽然显示了两次，但每一行的JOB_ID是不同的。  
  
考虑下面的例子：  
SELECT employee_id, job_id, department_id  
FROM employees  
UNION  
SELECT employee_id, job_id, department_id  
FROM job_history;  
  
  
EMPLOYEE_ID JOB_ID      DEPARTMENT_ID  
100  
200     AC_ACCOUNT  90  
200     AD_ASST     10  
200     AD_ASST     90  
  
...  
600  
  
使用联合集合(UNION SET)运算 (续)  
    在前面的输出中，雇员200出现了3次，为什么？注意雇员200的DEPARTMENT_ID值，一行是90，另一行是10，第三行是90，因为这些job_id和department_id的唯一组合，对于每行的雇员200是唯一的，因此他们是不重复的。  
     
    观察输出以SELECT子句的第一列的升序排序，即以EMPLOYEE_ID排序。  
  
  
[b]全联合运算[/b]  
  
    全联合(UNION ALL) 运算从两个查询返回包括所有重复值的结果  
  
  
全联合(UNION ALL)运算  
用全联合运算从多个查询中返回所有行。  
  
原则  ./././././././././  
:: 和联合不同，重复的行不被过滤，并且默认情况下输出不排序。  
:: 不能使用DISTINCT关键字。  
  
   注：除了上面的两点，UNION ALL的原则与UNION相同。  
  
  
[b]使用全联合运算[/b]  
  
    显示当前和以前所有雇员所在的部门  
  
SELECT employee_id, job_id, department_id  
FROM employees  
UNION ALL  
SELECT employee_id, job_id, department_id  
FROM job_history  
ORDER BY employee_id;  
  
  
EMPLOYEE_ID JOB_ID      DEPARTMENT_ID  
100  
200     AD_ASST     10  
200     AD_ASST     90  
200     AC_ACCOUNT  90  
  
...  
600  
  
[b]  
全联合(UNION ALL)运算 (续)[/b]  
    在例子中，有30行被选择，两个表组合到共30行中，全联合运算不会消除重复的行，在幻灯片中重复的行被突出显示，联合返回任一查询所选择的所有不重复的行，而全联合返回任一查询所选择的所有行，包括所有重复。  
  
现在用联合子句来写：  
SELECT employee_id, job_id,department_id   
FROM employees   
UNION   
SELECT employee_id, job_id,department_id   
FROM job_history   
ORDER BY employee_id;  
  
上面的查询返回29行，这是因为下面的行被除去了(因为它是一个重复行)：  
176 SA_REP      80  
  
  
[b]  
相交运算  
[/b]  
   相交(INTERSECT) 运算返回多个查询中所有相同的行  
  
相交运算  
      用相交运算返回多个查询中所有的公共行。  
  
原则      ./././.列数和数据类型  
:: 在查询中被 SELECT 语句选择的列数和数据类型必须与在查询中所使用的所有的 SELTCT 语句中的   一样，但列的名字不必一样。  
:: 颠倒相交的表的排序不改变结果。  
:: 相交不忽略空值。  
  
[b]使用相交运算[/b]  
  
      显示雇员表的employee_ID 和job_ID，这些雇员当前所做的工作是以前他们做过一端时间，后来有变化，现在又在做的工作。  
  
SQL> SELECT employee_id, job_id  
  2  FROM employees  
  3  INTERSECT  
  4  SELECT employee_id, job_id  
  5  FROM job_history;  
  
EMPLOYEE_ID JOB_ID  
----------- ----------  
        176 SA_REP  
        200 AD_ASST  
  
  
相交运算 (续)  
    在幻灯片的例子中，查询仅返回在两个表的被选择的列中有相同值的记录。  
  
    如果你从EMPLOYEES表添加DEPARTMENT_ID列到SELECT语句中，并且从JOB_HISTORY表添加DEPARTMENT_ID列到SELECT语句中，然后运行该查询，将返回什么？其结果可能是不同的，因为在加入的另一个列中，其值可能是重复的，也可能不重复。  
  
例子  
SELECT employee_id, job_id, department_id  
FROM employees  
INTERSECT  
SELECT employee_id, job_id, department_id  
FROM job_history;  
  
EMPLOYEE_ID JOB_ID     DEPARTMENT_ID  
----------- ---------- -------------  
        176 SA_REP                80  
  
雇员200不再是结果的一部分，因为EMPLOYEES.DEPARTMENT_ID值不同于 JOB_HISTORY.DEPARTMENT_ID值。  
  
  
[b]相减运算 [/b]  
  
    相减(MINUS) 运算返回在第一个查询中而不在第二个查询中的行  
  
相减运算  
    用相减运算返回由第一个查询返回的行，那些行不出现在第二个查询中 (第一个SELECT语句减第二个SELECT语句)。  
  
原则  ./././././././  
:: 在查询中被SELECT语句选择的列数和数据类型必须与在查询中所使用的所有的SELTCT语句中的一样    ，但列的名字不必一样。  
:: 对于MINUS运算，在WHERE子句中所有的列都必须在SELECT子句中。  
  
相减运算  
     
   显示那些从来没有改变过他们的工作的雇员显示那些从来没有改变过他们的工作的雇员ID  
  
SELECT employee_id,job_id  
FROM employees  
MINUS  
SELECT employee_id,job_id  
FROM job_history;  
//由存在于EMPLOYEES表中但不存在于JOB_HISTORY表中的行所表示(结果只是employees表的减少,没有第二个表的记录.)  
  
相减运算 (续)  
     上述例子中，JOB_HISTORY表的employee_id列和job_id列被从EMPLOYEES表的那些列中减去。结果集显示相减后剩余的雇员，他们由存在于EMPLOYEES表中但不存在于JOB_HISTORY表中的行所表示。这些行是那些从未改变过工作的雇员的记录  
  
雇员200未出现，因为他换过工作。(即,在第一个,第二个表中都出现了)  
  
  
[b]集合运算的原则[/b]  /././././././  
  
:: 在两个SELECT列表中的表达式必须在数目上和数据类型上相匹配  
:: 可以用圆括号改变执行的顺序  
  
:: ORDER BY子句：  /./././././  
    –只能出现在语句的最后  
    –从第一个SELECT语句接收列名、别名，或者位置记号  
  
  
集合运算的原则  
  
:: 在两个查询的选择列表中的表达式在数目上和数据类型上必须匹配。使用UNION、UNION ALL、INTERSECT和MINUS SET运算的查询，在它们的WHERE子句中必须有与它们的SELECT列表相同的列数和列数据类型。  
  
例如：  
SELECT employee_id, department_id  
FROM employees  
WHERE (employee_id, department_id)  
IN (SELECT employee_id, department_id  
    FROM employees  
    UNION  
    SELECT employee_id, department_id  
    FROM job_history  
);  
  
:: ORDER BY子句：  
    - 只可以出现在每个语句的末尾  
    - 将接受列、别名或位置记号  
  
:: 列名或别名，如果用在ORDER BY子句中，必须来自第一个SELECT列表。  
:: 集合运算可以用在子查询中。  
  
  
  
[b]Oracle 服务器和集合运算[/b]  
  
:: 除了UNION ALL，重复行自动被清除  
:: 在结果中的列名是第一个查询中出现的列名  /./././  
:: 除了UNION ALL(无排序)，默认情况下按升序顺序输出    /././  
  
Oracle服务器和集合运算  
   当一个查询使用集合运算时，除了UNION ALL运算，Oracle服务器会自动消除重复的行。输出中的列名由第一个SELECT语句的列表确定。默认情况下，输出以SELECT子句的第一列的升序排序。  
  
   在一个复合查询的各查询组成部分的选择列表中相应的表达式必须在数目和类型上匹配。如果查询的组成部分选择字符数据，返回值的数据类型被如下决定：  
 :: 如果查询选择的数据类型的值为CHAR，那么，返回值的数据类型也为CHAR。  
 :: 如果查询选择的两者之一或两者的数据类型值为VARCHAR2，那么，返回值的数据类型也是VARCHAR2。  
  
  你可能需要提及，输出以第一个SELECT子句的第一列的升序被排序，然后是第二列，等等。  
  
  
  
匹配SELECT语句  //凑数  
  
   使用UNION运算，显示所有雇员的department ID、location、和受雇日期  
  
  
SELECT department_id, TO_NUMBER(null) location, hire_date  
FROM employees  
UNION  
SELECT department_id, location_id, TO_DATE(null)  
FROM departments;  
  
DEPARTMENT_ID   LOCATION HIRE_DATE  
------------- ---------- ----------  
           80            21-4月 -00  
           90       1700  
           90            17-6月 -87  
           90            21-9月 -89  
           90            13-1月 -93  
          100       1700  
          100            16-8月 -94  
          100            17-8月 -94  
          100            28-9月 -97  
          100            30-9月 -97  
          100            07-3月 -98  
  
匹配SELECT语句  
    由于在两个查询的SELECT列表中的表达式必须在数量上匹配，你可以使用虚拟列和转换函数数据类型来满足该规则。在幻灯片中使用了虚拟列，在第一个查询中的TO_NUMBER函数被用以匹配第二个查询中返回的LOCATION_ID列的数字数据类型，同样地，第二个查询中的TO_DATE函数被用于匹配 第一个查询返回的日期数据类型。  
  
[b]匹配SELECT语句[/b]  
  
:: 使用UNION运算，显示所有雇员的employee ID、job ID 和salary  
  
SELECT employee_id, job_id,salary  
FROM employees  
UNION  
SELECT employee_id, job_id,0  
FROM job_history;  
  
EMPLOYEE_ID JOB_ID         SALARY  
----------- ---------- ----------  
        192 SH_CLERK         4000  
        193 SH_CLERK         3900  
        194 SH_CLERK         3200  
        195 SH_CLERK         2800  
        196 SH_CLERK         3100  
        197 SH_CLERK         3000  
        198 SH_CLERK         2600  
        199 SH_CLERK         2600  
        200 AC_ACCOUNT          0  
        200 AD_ASST             0  
        200 AD_ASST          4400  
  
  
匹配SELECT语句：例  
      EMPLOYEES和JOB_HISTORY表中有一些列是公有的；例如，EMPLOYEE_ID, JOB_ID和DEPARTMENT_ID，但如果你想要用UNION运算查询他们以显示EMPLOYEE_ID、JOB_ID和SALARY时，知道薪水仅存在于EMPLOYEES表中吗？  
  
      在幻灯片中的代码例子匹配EMPLOYEES表和JOB_HISTORY表中的EMPLOYEE_ID和JOB_ID列。一个0值被添加到JOB_HISTORY的SELECT语句中以匹配在EMPLOYEES的SELECT语句中的数字的SALARY列。  
在前面的结果中，在输出中的每一行相对应于JOB_HISTORY表的一条记录，包括SALARY列值为0的列。  
  
  
  
[b]控制行顺序[/b]  
     
    用两个UNION运算产生一个英语句子  
  
COLUMN a_dummy NOPRINT  
SELECT 'sing' AS "My dream", 3 a_dummy  
FROM dual  
UNION  
SELECT 'I''d like to teach', 1  
FROM dual  
UNION   
SELECT 'the world to', 2  
FROM dual  
ORDER BY 2;  
  
ORDER BY 只能用列、别名或第一个查询的列位置。  
  
控制行顺序  
  在默认情况下，输出以第一列的升序排序，你可以用ORDER BY子句来改变顺序。  
  
使用ORDER BY来改变行的顺序  
  ORDER BY子句在复合查询中只能用一次。如果使用了，ORDER BY子句必须放在最后面的查询中。ORDER BY子句接受列名、别名或位置记号。如果没有ORDER BY子句，幻灯片中的代码例子将按第一列的字母顺序产生下面的输出：  
            My dream  
i'd like to teach  
sing  
the world to  
        /././././  
  注：考虑一个多次使用UNION SET运算的复合查询，在这种情况下，ORDER BY子句中只能用位置而不能显式表示。在幻灯片的例子中3个SELECT列表的第二个位置分别是3、1、2。另外，因为输出按照第一个SELECT列表，所以别名My dream定义在第一行；指示第二列不输出的a_dummy也在第一列。
0 0