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

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

--6.2复杂查询--6.2.1多表连接查询--内连接(Oracle 特有语法)select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dnamefrom emp,deptwhere emp.deptno=dept.deptno; --内连接(ANSI SQL语法)，与上述SQL语句等价select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dnamefrom emp inner join dept on emp.deptno=dept.deptno; --外连接(左外连接)select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dnamefrom emp,deptwhere emp.deptno=dept.deptno(+);--等价ANSI SQL语法select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dnamefrom emp left outer join dept on emp.deptno=dept.deptno;--外连接(右外连接)select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dnamefrom emp,deptwhere emp.deptno(+)=dept.deptno;--等价ANSI SQL语法select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dnamefrom emp right outer join dept on emp.deptno=dept.deptno;--全连接select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dnamefrom emp full outer join dept on emp.deptno=dept.deptno;--交叉连接select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dnamefrom emp,deptselect 22*6 from dual;--自然连接（首次使用）select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,dept.dnamefrom 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 = 20UNIONSELECT   empno, ename, sal, hiredate, deptno    FROM emp_history   WHERE deptno = 30;   --联合运算（Union all,包括重复行）   SELECT   empno, ename, sal, hiredate, deptno    FROM emp   WHERE deptno = 20UNION ALLSELECT   empno, ename, sal, hiredate, deptno    FROM emp_history   WHERE deptno = 20;     --相交运算   SELECT   empno, ename, sal, hiredate, deptno    FROM emp   WHERE deptno = 20INTERSECTSELECT   empno, ename, sal, hiredate, deptno    FROM emp_history   WHERE deptno = 20;        --相减运算   SELECT   empno, ename, sal, hiredate, deptno    FROM emp   WHERE deptno = 20MINUSSELECT   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_idCONNECT 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;--查询语句1select 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 astart with a.parent is null --从parent为空开始扫描connect by prior a.child = a.parent --以child为父列连接parentorder siblings by child desc --对层次排序;--查询语句2Select level,connect_by_iscycle,connect_by_isleaf,parent,childFrom tab_connect_byConnect by nocycle prior child= parentStart with parent is null;