选读SQL经典实例笔记14_层次查询

1. 结果集

1.1. sql

1. select empno,mgr
2.   from emp
3. order by 2
4.     EMPNO        MGR
5. ---------- ----------
6.       7788       7566
7.       7902       7566
8.       7499       7698
9.       7521       7698
10.       7900       7698
11.       7844       7698
12.       7654       7698
13.       7934       7782
14.       7876       7788
15.       7566       7839
16.       7782       7839
17.       7698       7839
18.       7369       7902
19.       7839

复制代码

2. 展现父子关系

2.1. 结果集

2.1.1. sql

1. EMPS_AND_MGRS
2. ------------------------------
3. FORD works for JONES
4. SCOTT works for JONES
5. JAMES works for BLAKE
6. TURNER works for BLAKE
7. MARTIN works for BLAKE
8. WARD works for BLAKE
9. ALLEN works for BLAKE
10. MILLER works for CLARK
11. ADAMS works for SCOTT
12. CLARK works for KING
13. BLAKE works for KING
14. JONES works for KING
15. SMITH works for FORD

复制代码

2.2. DB2

2.3. Oracle

2.4. PostgreSQL

2.5. 自连接EMP表

2.5.1. sql

1. select a.ename || ' works for ' || b.ename as emps_and_mgrs
2.   from emp a, emp b
3. where a.mgr = b.empno

复制代码

2.6. MySQL

2.6.1. CONCAT函数连接字符串

2.6.1.1. sql

1. select concat(a.ename, ' works for ',b.ename) as emps_and_mgrs
2.   from emp a, emp b
3. where a.mgr = b.empno

复制代码

2.7. SQL Server

2.7.1. 加号“+”连接字符串

2.7.1.1. sql

1. select a.ename + ' works for ' + b.ename as emps_and_mgrs
2.   from emp a, emp b
3. where a.mgr = b.empno

复制代码

3. 展现祖孙关系

3.1. 结果集

3.1.1. sql

1. select ename,empno,mgr
2.   from emp
3. where ename in ('KING','CLARK','MILLER')
4. ENAME           EMPNO        MGR
5. ---------- ---------- ----------
6. CLARK            7782       7839
7. KING             7839
8. MILLER           7934       7782

复制代码

3.1.2. sql

1. LEAF___BRANCH_ _ _ROOT
2. ----------------------
3. MILLER--＞CLARK--＞KING

复制代码

3.2. DB2

3.3. SQL Server

3.4. WITH递归查询

3.4.1.   sql

1. with x (tree,mgr,depth)
2.     as (
3. select cast(ename as varchar(100)),
4.         mgr, 0
5.   from emp
6. where ename = 'MILLER'
7. union all
8. select cast(x.tree+'--＞'+e.ename as varchar(100)),
9.         e.mgr, x.depth+1
10.   from emp e, x
11. where x.mgr = e.empno
12. )
13. select tree leaf___branch___root
14.   from x
15. where depth = 2

复制代码

3.4.2. SQL Server的字符串连接操作符+

3.4.3. DB2的字符串连接操作符||

3.5. Oracle

3.5.1. SYS_CONNECT_BY_PATH函数

3.5.1.1.  sql

1. select ltrim(
2.           sys_connect_by_path(ename,'--＞'),
3.         '--＞') leaf___branch___root
4.    from emp
5.   where level = 3
6.   start with ename = 'MILLER
7. connect by prior mgr = empno

复制代码

3.6. PostgreSQL

3.7. MySQL

3.8. 自连接两次

3.8.1. sql

1. select a.ename||'--＞'||b.ename
2.               ||'--＞'||c.ename as leaf___branch___root
3.   from emp a, emp b, emp c
4. where a.ename = 'MILLER'
5.    and a.mgr = b.empno
6.    and b.mgr = c.empno

复制代码

3.8.2. MySQL使用CONCAT函数

4. 创建层次视图

4.1. 结果集

4.1.1. sql

1. EMP_TREE
2. -------------------------------
3. KING
4. KING - BLAKE
5. KING - BLAKE - ALLEN
6. KING - BLAKE - JAMES
7. KING - BLAKE - MARTIN
8. KING - BLAKE - TURNER
9. KING - BLAKE - WARD
10. KING - CLARK
11. KING - CLARK - MILLER
12. KING - JONES
13. KING - JONES - FORD
14. KING - JONES - FORD - SMITH
15. KING - JONES - SCOTT
16. KING - JONES - SCOTT – ADAMS

复制代码

4.2. DB2

4.3. SQL Server

4.4. WITH递归查询

4.4.1.   sql

1. with x (ename,empno)
2.      as (
3. select cast(ename as varchar(100)),empno
4.    from emp
5.   where mgr is null
6.   union all
7. select cast(x.ename||' - '||e.ename as varchar(100)),
8.         e.empno
9.    from emp e, x
10.   where e.mgr = x.empno
11. )
12. select ename as emp_tree
13.    from x
14.   order by 1

复制代码

4.4.2. SQL Server使用字符串连接操作符 +

4.5. Oracle

4.5.1. CONNECT BY函数

4.5.1.1.  sql

1. select ltrim(
2.           sys_connect_by_path(ename,' - '),
3.         ' - ') emp_tree
4.    from emp
5.   start with mgr is null
6. connect by prior empno=mgr
7.   order by 1

复制代码

4.6. PostgreSQL

4.6.1.  sql

1. select emp_tree
2.    from (
3. select ename as emp_tree
4.    from emp
5.   where mgr is null
6. union
7. select a.ename||' - '||b.ename
8.    from emp a
9.         join
10.         emp b on (a.empno=b.mgr)
11.   where a.mgr is null
12. union
13. select rtrim(a.ename||' - '||b.ename
14.                      ||' - '||c.ename,' - ')
15.   from emp a
16.        join
17.        emp b on (a.empno=b.mgr)
18.        left join
19.        emp c on (b.empno=c.mgr)
20. where a.ename = 'KING'
21. union
22. select rtrim(a.ename||' - '||b.ename||' - '||
23.              c.ename||' - '||d.ename,' - ')
24.   from emp a
25.        join
26.        emp b on (a.empno=b.mgr)
27.        join
28.        emp c on (b.empno=c.mgr)
29.        left join
30.        emp d on (c.empno=d.mgr)
31. where a.ename = 'KING'
32.        ) x
33. where tree is not null
34. order by 1

复制代码

4.7. MySQL

4.7.1.  sql

1. select emp_tree
2.    from (
3. select ename as emp_tree
4.    from emp
5.   where mgr is null
6. union
7. select concat(a.ename,' - ',b.ename)
8.   from emp a
9.        join
10.        emp b on (a.empno=b.mgr)
11. where a.mgr is null
12. union
13. select concat(a.ename,' - ',
14.               b.ename,' - ',c.ename)
15.   from emp a
16.        join
17.        emp b on (a.empno=b.mgr)
18.        left join
19.        emp c on (b.empno=c.mgr)
20. where a.ename = 'KING'
21. union
22. select concat(a.ename,' - ',b.ename,' - ',
23.               c.ename,' - ',d.ename)
24.   from emp a
25.        join
26.        emp b on (a.empno=b.mgr)
27.        join
28.        emp c on (b.empno=c.mgr)
29.        left join
30.        emp d on (c.empno=d.mgr)
31. where a.ename = 'KING'
32.        ) x
33. where tree is not null
34. order by 1

复制代码

5. 给定的父节点对应的所有子节点

5.1. 结果集

5.1.1. sql

1. ENAME
2. ---------
3. JONES
4. SCOTT
5. ADAMS
6. FORD
7. SMITH

复制代码

5.2. DB2

5.3. SQL Server

5.4. WITH递归查询

5.4.1.     sql

1. with x (ename,empno)
2.       as (
3.   select ename,empno
4.     from emp
5.    where ename = 'JONES'
6.    union all
7.   select e.ename, e.empno
8.     from emp e, x
9.    where x.empno = e.mgr
10.   )
11.   select ename
12.     from x

复制代码

5.5. Oracle

5.5.1. CONNECT BY子句

5.5.1.1.  sql

1. select ename
2.    from emp
3.   start with ename = 'JONES'
4. connect by prior empno = mgr

复制代码

5.6. PostgreSQL

5.7. MySQL

5.8. 自连接

5.8.1. sql

1. create view v1
2. as
3. select ename,mgr,empno
4.   from emp
5. where ename = 'JONES'
7. create view v2
8. as
9. select ename,mgr,empno
10.   from emp
11. where mgr = (select empno from v1)
13. create view v3
14. as
15. select ename,mgr,empno
16.   from emp
17. where mgr in (select empno from v2)

复制代码

5.8.2. sql

1. select ename from v1
2. union
3. select ename from v2
4. union
5. select ename from v3

复制代码

5.8.3. 需要提前知道层次关系的深度

6. 确认叶子节点、分支节点和根节点

6.1. 结果集

6.1.1. sql

1. ENAME         IS_LEAF  IS_BRANCH    IS_ROOT
2. ---------- ---------- ---------- ----------
3. KING                 0         0          1
4. JONES                0         1          0
5. SCOTT                0         1          0
6. FORD                 0         1          0
7. CLARK                0         1          0
8. BLAKE                0         1          0
9. ADAMS                1         0          0
10. MILLER               1         0          0
11. JAMES                1         0          0
12. TURNER               1         0          0
13. ALLEN                1         0          0
14. WARD                 1         0          0
15. MARTIN               1         0          0
16. SMITH                1         0          0

复制代码

6.2. DB2

6.3. PostgreSQL

6.4. MySQL

6.5. SQL Server

6.6. 3个标量子查询

6.6.1. sql

1. select e.ename,
2.        (select sign(count(*)) from emp d
3.          where 0 =
4.            (select count(*) from emp f
5.              where f.mgr = e.empno)) as is_leaf,
6.        (select sign(count(*)) from emp d
7.          where d.mgr = e.empno
8.            and e.mgr is not null) as is_branch,
9.        (select sign(count(*)) from emp d
10.          where d.empno = e.empno
11.            and d.mgr is null) as is_root
12.    from emp e
13. order by 4 desc,3 desc

复制代码

6.7. Oracle

6.7.1.  sql

1. select ename,
2.         connect_by_isleaf is_leaf,
3.         (select count(*) from emp e
4.           where e.mgr = emp.empno
5.             and emp.mgr is not null
6.             and rownum = 1) is_branch,
7.         decode(ename,connect_by_root(ename),1,0) is_root
8.    from emp
9.   start with mgr is null
10. connect by prior empno = mgr
11. order by 4 desc, 3 desc

复制代码

6.7.1.1. Oracle Database 10g新增的CONNECT_BY_ROOT和CONNECT_BY_ISLEAF


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！