(相关资料图)
1.结果集
1.1.sql
select empno,mgr from emporder by 2 EMPNO MGR---------- ---------- 7788 7566 7902 7566 7499 7698 7521 7698 7900 7698 7844 7698 7654 7698 7934 7782 7876 7788 7566 7839 7782 7839 7698 7839 7369 7902 78392.展现父子关系
2.1.结果集
2.1.1.sql
EMPS_AND_MGRS------------------------------FORD works for JONESSCOTT works for JONESJAMES works for BLAKETURNER works for BLAKEMARTIN works for BLAKEWARD works for BLAKEALLEN works for BLAKEMILLER works for CLARKADAMS works for SCOTTCLARK works for KINGBLAKE works for KINGJONES works for KINGSMITH works for FORD2.2.DB2
2.3.Oracle
2.4.PostgreSQL
2.5.自连接EMP表
2.5.1.sql
select a.ename || " works for " || b.ename as emps_and_mgrs from emp a, emp b where a.mgr = b.empno2.6.MySQL
2.6.1.CONCAT函数连接字符串
2.6.1.1.sql
select concat(a.ename, " works for ",b.ename) as emps_and_mgrs from emp a, emp b where a.mgr = b.empno2.7.SQL Server
2.7.1.加号“+”连接字符串
2.7.1.1.sql
select a.ename + " works for " + b.ename as emps_and_mgrs from emp a, emp b where a.mgr = b.empno3.展现祖孙关系
3.1.结果集
3.1.1.sql
select ename,empno,mgr from emp where ename in ("KING","CLARK","MILLER")ENAME EMPNO MGR---------- ---------- ----------CLARK 7782 7839KING 7839MILLER 7934 77823.1.2.sql
LEAF___BRANCH_ _ _ROOT----------------------MILLER--＞CLARK--＞KING3.2.DB2
3.3.SQL Server
3.4.WITH递归查询
3.4.1.sql
with x (tree,mgr,depth) as (select cast(ename as varchar(100)), mgr, 0 from emp where ename = "MILLER"union allselect cast(x.tree+"--＞"+e.ename as varchar(100)), e.mgr, x.depth+1 from emp e, x where x.mgr = e.empno)select tree leaf___branch___root from x where depth = 23.4.2.SQL Server的字符串连接操作符+
3.4.3.DB2的字符串连接操作符||
3.5.Oracle
3.5.1.SYS_CONNECT_BY_PATH函数
3.5.1.1.sql
select ltrim( sys_connect_by_path(ename,"--＞"), "--＞") leaf___branch___root from emp where level = 3 start with ename = "MILLERconnect by prior mgr = empno3.6.PostgreSQL
3.7.MySQL
3.8.自连接两次
3.8.1.sql
select a.ename||"--＞"||b.ename ||"--＞"||c.ename as leaf___branch___root from emp a, emp b, emp c where a.ename = "MILLER" and a.mgr = b.empno and b.mgr = c.empno3.8.2.MySQL使用CONCAT函数
4.创建层次视图
4.1.结果集
4.1.1.sql
EMP_TREE-------------------------------KINGKING - BLAKEKING - BLAKE - ALLENKING - BLAKE - JAMESKING - BLAKE - MARTINKING - BLAKE - TURNERKING - BLAKE - WARDKING - CLARKKING - CLARK - MILLERKING - JONESKING - JONES - FORDKING - JONES - FORD - SMITHKING - JONES - SCOTTKING - JONES - SCOTT – ADAMS4.2.DB2
4.3.SQL Server
4.4.WITH递归查询
4.4.1.sql
with x (ename,empno) as ( select cast(ename as varchar(100)),empno from emp where mgr is null union all select cast(x.ename||" - "||e.ename as varchar(100)), e.empno from emp e, x where e.mgr = x.empno ) select ename as emp_tree from x order by 14.4.2.SQL Server使用字符串连接操作符 +
4.5.Oracle
4.5.1.CONNECT BY函数
4.5.1.1.sql
select ltrim( sys_connect_by_path(ename," - "), " - ") emp_tree from emp start with mgr is nullconnect by prior empno=mgr order by 14.6.PostgreSQL
4.6.1.sql
select emp_tree from ( select ename as emp_tree from emp where mgr is null union select a.ename||" - "||b.ename from emp a join emp b on (a.empno=b.mgr) where a.mgr is null union select rtrim(a.ename||" - "||b.ename ||" - "||c.ename," - ") from emp a join emp b on (a.empno=b.mgr) left join emp c on (b.empno=c.mgr) where a.ename = "KING"unionselect rtrim(a.ename||" - "||b.ename||" - "|| c.ename||" - "||d.ename," - ") from emp a join emp b on (a.empno=b.mgr) join emp c on (b.empno=c.mgr) left join emp d on (c.empno=d.mgr) where a.ename = "KING" ) x where tree is not null order by 14.7.MySQL
4.7.1.sql
select emp_tree from ( select ename as emp_tree from emp where mgr is null unionselect concat(a.ename," - ",b.ename) from emp a join emp b on (a.empno=b.mgr) where a.mgr is nullunionselect concat(a.ename," - ", b.ename," - ",c.ename) from emp a join emp b on (a.empno=b.mgr) left join emp c on (b.empno=c.mgr) where a.ename = "KING"unionselect concat(a.ename," - ",b.ename," - ", c.ename," - ",d.ename) from emp a join emp b on (a.empno=b.mgr) join emp c on (b.empno=c.mgr) left join emp d on (c.empno=d.mgr) where a.ename = "KING" ) x where tree is not null order by 15.给定的父节点对应的所有子节点
5.1.结果集
5.1.1.sql
ENAME---------JONESSCOTTADAMSFORDSMITH5.2.DB2
5.3.SQL Server
5.4.WITH递归查询
5.4.1.sql
with x (ename,empno) as ( select ename,empno from emp where ename = "JONES" union all select e.ename, e.empno from emp e, x where x.empno = e.mgr ) select ename from x5.5.Oracle
5.5.1.CONNECT BY子句
5.5.1.1.sql
select ename from emp start with ename = "JONES"connect by prior empno = mgr5.6.PostgreSQL
5.7.MySQL
5.8.自连接
5.8.1.sql
create view v1asselect ename,mgr,empno from emp where ename = "JONES"create view v2asselect ename,mgr,empno from emp where mgr = (select empno from v1)create view v3asselect ename,mgr,empno from emp where mgr in (select empno from v2)5.8.2.sql
select ename from v1 unionselect ename from v2 unionselect ename from v35.8.3.需要提前知道层次关系的深度
6.确认叶子节点、分支节点和根节点
6.1.结果集
6.1.1.sql
ENAME IS_LEAF IS_BRANCH IS_ROOT---------- ---------- ---------- ----------KING 0 0 1JONES 0 1 0SCOTT 0 1 0FORD 0 1 0CLARK 0 1 0BLAKE 0 1 0ADAMS 1 0 0MILLER 1 0 0JAMES 1 0 0TURNER 1 0 0ALLEN 1 0 0WARD 1 0 0MARTIN 1 0 0SMITH 1 0 06.2.DB2
6.3.PostgreSQL
6.4.MySQL
6.5.SQL Server
6.6.3个标量子查询
6.6.1.sql
select e.ename, (select sign(count(*)) from emp d where 0 = (select count(*) from emp f where f.mgr = e.empno)) as is_leaf, (select sign(count(*)) from emp d where d.mgr = e.empno and e.mgr is not null) as is_branch, (select sign(count(*)) from emp d where d.empno = e.empno and d.mgr is null) as is_root from emp e order by 4 desc,3 desc6.7.Oracle
6.7.1.sql
select ename, connect_by_isleaf is_leaf, (select count(*) from emp e where e.mgr = emp.empno and emp.mgr is not null and rownum = 1) is_branch, decode(ename,connect_by_root(ename),1,0) is_root from emp start with mgr is nullconnect by prior empno = mgrorder by 4 desc, 3 desc6.7.1.1.Oracle Database 10g新增的CONNECT_BY_ROOT和CONNECT_BY_ISLEAF
关键词：
