源码-PL/SQL从入门到精通-第六章-查询数据表-Part 3

层次化查询为首次接触，还没有吃透。

--6.2复杂查询
--6.2.1多表连接查询
--内连接(Oracle 特有语法)
select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dname
from emp,dept
where emp.deptno=dept.deptno;
 
--内连接(ANSI SQL语法)，与上述SQL语句等价
select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dname
from emp inner join dept on emp.deptno=dept.deptno;
 
--外连接(左外连接)
select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dname
from emp,dept
where emp.deptno=dept.deptno(+);

--等价ANSI SQL语法
select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dname
from emp left outer join dept on emp.deptno=dept.deptno;

--外连接(右外连接)
select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dname
from emp,dept
where emp.deptno(+)=dept.deptno;

--等价ANSI SQL语法
select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dname
from emp right outer join dept on emp.deptno=dept.deptno;

--全连接
select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dname
from emp full outer join dept on emp.deptno=dept.deptno;


--交叉连接
select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dname
from emp,dept

select 22*6 from dual;

--自然连接（首次使用）
select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dname
from emp natural join dept;

--6.2.2 使用子查询

--非相关子查询
SELECT *
  FROM emp
 WHERE sal > (SELECT sal
                FROM emp
               WHERE ename = 'SMITH');

SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE job = (SELECT job
                FROM emp
               WHERE ename = 'SMITH');
               

SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE job = (SELECT MIN(sal)
                FROM emp
               WHERE ename = 'SMITH');               
               
             
SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE sal = (SELECT MIN (sal)
                FROM emp);                   
                
               
SELECT empno, ename, job, mgr, hiredate, sal, deptno
  FROM emp
 WHERE sal IN (SELECT   MIN (sal)
                   FROM emp
               GROUP BY deptno);    
               
               
SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE sal >some(SELECT sal FROM emp WHERE job='CLERK') 
 AND job<>'CLERK';                   
 
SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE sal > ALL (SELECT sal
                    FROM emp
                   WHERE job = 'CLERK') AND job <> 'CLERK';
                   
                   
--相关子查询（不太容易理解）
SELECT   e1.empno, e1.ename, e1.deptno
    FROM emp e1
   WHERE e1.sal > (SELECT AVG (sal)
                     FROM emp e2
                    WHERE e2.deptno = e1.deptno)
ORDER BY e1.deptno
                   


--6.2.3 表集合操作
SELECT * FROM emp;  
                 
--联合运算（Union, 将去除重复行）

CREATE TABLE emp_history AS SELECT * FROM emp;

SELECT   empno, ename, sal, hiredate, deptno
    FROM emp
   WHERE deptno = 20
UNION
SELECT   empno, ename, sal, hiredate, deptno
    FROM emp_history
   WHERE deptno = 30;
   
--联合运算（Union all,包括重复行）   
SELECT   empno, ename, sal, hiredate, deptno
    FROM emp
   WHERE deptno = 20
UNION ALL
SELECT   empno, ename, sal, hiredate, deptno
    FROM emp_history
   WHERE deptno = 20;  
   
--相交运算   
SELECT   empno, ename, sal, hiredate, deptno
    FROM emp
   WHERE deptno = 20
INTERSECT
SELECT   empno, ename, sal, hiredate, deptno
    FROM emp_history
   WHERE deptno = 20;     
   
--相减运算   
SELECT   empno, ename, sal, hiredate, deptno
    FROM emp
   WHERE deptno = 20
MINUS
SELECT   empno, ename, sal, hiredate, deptno
    FROM emp_history
   WHERE deptno = 20;        
   
--6.2.4 层次化查询  
SELECT * FROM employees;  
   
   
SELECT     LEVEL, LPAD ('  ', 2 * (LEVEL - 1)) || last_name "EmpName",
           hire_date, salary
      FROM employees
--表示根节点为
START WITH manager_id IS NULL
--PRIOR表示父行的employee_id，等于当前行的manager_id
CONNECT BY manager_id = PRIOR employee_id;

--构建测试表与插入测试语句
create table tab_connect_by (child number,parent number);
insert into tab_connect_by (CHILD, PARENT) values(2, 5);
insert into tab_connect_by (CHILD, PARENT) values(3, 5);
insert into tab_connect_by (CHILD, PARENT) values(10, 15);
insert into tab_connect_by (CHILD, PARENT) values(5, 15);
insert into tab_connect_by (CHILD, PARENT) values(9, 17);
insert into tab_connect_by (CHILD, PARENT) values(8, 17);
insert into tab_connect_by (CHILD, PARENT) values(15, 38);
insert into tab_connect_by (CHILD, PARENT) values(17, 38);
insert into tab_connect_by (CHILD, PARENT) values(6, 38);
insert into tab_connect_by (CHILD, PARENT) values(13, 26);
insert into tab_connect_by (CHILD, PARENT) values(1, 26);
insert into tab_connect_by (CHILD, PARENT) values(12, 26);
insert into tab_connect_by (CHILD, PARENT) values(11, 18);
insert into tab_connect_by (CHILD, PARENT) values(7, 18);
insert into tab_connect_by (CHILD, PARENT) values(38, null);
insert into tab_connect_by (CHILD, PARENT) values(26, null);
insert into tab_connect_by (CHILD, PARENT) values(18, null);
commit;

select * from tab_connect_by;

--查询语句1
select a.child,
a.parent,
level "层次",
sys_connect_by_path(child, '<-') "合并层次",
prior a.child "父节点",
connect_by_root a.child "根节点",
decode(connect_by_isleaf, 1, a.child, null) "子节点",
decode(connect_by_isleaf, 1, '是', '否') "是否子节点"
from tab_connect_by a
start with a.parent is null --从parent为空开始扫描
connect by prior a.child = a.parent --以child为父列连接parent
order siblings by child desc --对层次排序
;
--查询语句2
Select level,connect_by_iscycle,connect_by_isleaf,parent,child
From tab_connect_by
Connect by nocycle prior child= parent
Start with parent is null;