oracle 子查询、集合操作、复杂查询

--在DDL语句中使用子查询:
SQL> create table new_table(id,name,sal) as
  2  select empno,ename,sal from emp;

Table created.

SQL> create or replace view dept_10 as
  2  select empno,ename,job,sal,deptno from emp
  3  where deptno=10
  4  order by empno;

View created.

--集合操作符
--1.UNION,用于取得两个结果集的并集，自动去掉结果集中重复行
SQL> select ename,sal,job from emp where sal>2500
  2  union
  3  select ename,sal,job from emp where job='MANAGER';

姓名                  工资 JOB                                                  
---------- --------------- ---------                                            
BLAKE               ￥2850 MANAGER                                              
CLARK               ￥2450 MANAGER                                              
FORD                ￥3000 ANALYST                                              
JONES               ￥2975 MANAGER                                              
KING                ￥5000 PRESIDENT                                            
SCOTT               ￥3000 ANALYST                                              

6 rows selected.
--2.UNION ALL,用于取得结果集的并集，不会取消重复值
SQL> select ename,sal,job from emp where sal>2500
  2  union all
  3  select ename,sal,job from emp where job='MANAGER';

姓名                  工资 JOB                                                  
---------- --------------- ---------                                            
JONES               ￥2975 MANAGER                                              
BLAKE               ￥2850 MANAGER                                              
SCOTT               ￥3000 ANALYST                                              
KING                ￥5000 PRESIDENT                                            
FORD                ￥3000 ANALYST                                              
JONES               ￥2975 MANAGER                                              
BLAKE               ￥2850 MANAGER                                              
CLARK               ￥2450 MANAGER                                              

8 rows selected.
--3.INTERSECT,用于取得两个结果集的交集

SQL> select ename,sal,job from emp where sal>2500
  2  intersect
  3  select ename,sal,job from emp where job='MANAGER';

姓名                  工资 JOB                                                  
---------- --------------- ---------                                            
BLAKE               ￥2850 MANAGER                                              
JONES               ￥2975 MANAGER     
--4.MINUS,用于取得两个结果集的差集。只显示在第一个结果集存在，在第二个结果集不存在的数据
SQL> select ename,sal,job from emp where sal>2500
  2  minus
  3  select ename,sal,job from emp where job='MANAGER';

姓名                  工资 JOB                                                  
---------- --------------- ---------                                            
FORD                ￥3000 ANALYST                                              
KING                ￥5000 PRESIDENT                                            
SCOTT               ￥3000 ANALYST      

--层次查询
--start with用于指定层次查询的根行。 connect by用于指定父行和子行之间的关系。prior引用父行。
SQL> select level,lpad('*',3*(level-1))||ename ename,
  2  lpad('*',3*(level-1))||job job from emp
  3  where job<>'CLERK'
  4  start with mgr is null
  5  connect by mgr=prior empno;
       LEVEL 姓名            JOB                                                  
---------- --------------- ---------------                                      
         1 KING            PRESIDENT                                            
         2   *JONES          *MANAGER                                           
         3      *SCOTT          *ANALYST                                        
         3      *FORD           *ANALYST                                        
         2   *BLAKE          *MANAGER                                           
         3      *ALLEN          *SALESMAN                                       
         3      *WARD           *SALESMAN                                       
         3      *MARTIN         *SALESMAN                                       
         3      *TURNER         *SALESMAN                                       
         2   *CLARK          *MANAGER                                           

10 rows selected.

--CASE表达式
SQL> select ename,sal,case when sal>3000 then 3
  2  when sal>2000 then 2 else 1 end grade
  3  from emp
  4  where deptno=10;

姓名                       工资      GRADE                                      
--------------- --------------- ----------                                      
CLARK                    ￥2450          2                                      
KING                     ￥5000          3                                      
MILLER                   ￥1300          1     

--with子句重用子查询
SQL> with sum_table as(
  2  select deptno,sum(sal) sumsal
  3  from emp
  4  group by deptno)
  5  select * from sum_table;

    DEPTNO     SUMSAL                                                           
---------- ----------                                                           
        10       8750                                                           
        20      10875                                                           
        30       9400