选读SQL经典实例笔记14_层次查询
1.选读SQL经典实例笔记23_读后总结与感想兼导读2.选读SQL经典实例笔记01_检索和排序3.选读SQL经典实例笔记02_多表查询4.选读SQL经典实例笔记03_DML和元数据5.选读SQL经典实例笔记04_日期运算(上)6.选读SQL经典实例笔记05_日期运算(下)7.选读SQL经典实例笔记06_日期处理(上)8.选读SQL经典实例笔记07_日期处理(下)9.选读SQL经典实例笔记08_区间查询10.选读SQL经典实例笔记09_数值处理11.选读SQL经典实例笔记10_高级查询12.选读SQL经典实例笔记11_结果集变换13.选读SQL经典实例笔记12_桶、图和小计14.选读SQL经典实例笔记13_case与聚合
15.选读SQL经典实例笔记14_层次查询
16.选读SQL经典实例笔记15_窗口函数17.选读SQL经典实例笔记16_逻辑否定18.选读SQL经典实例笔记17_最多和最少19.选读SQL经典实例笔记18_Exactly20.选读SQL经典实例笔记19_Any和All21.选读SQL经典实例笔记20_Oracle语法示例22.选读SQL经典实例笔记21_字符串处理23.选读SQL经典实例笔记22_2版增补1. 结果集
1.1. sql
select empno,mgr
from emp
order 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
7839
2. 展现父子关系
2.1. 结果集
2.1.1. sql
EMPS_AND_MGRS
------------------------------
FORD works for JONES
SCOTT works for JONES
JAMES works for BLAKE
TURNER works for BLAKE
MARTIN works for BLAKE
WARD works for BLAKE
ALLEN works for BLAKE
MILLER works for CLARK
ADAMS works for SCOTT
CLARK works for KING
BLAKE works for KING
JONES works for KING
SMITH works for FORD
2.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.empno
2.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.empno
2.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.empno
3. 展现祖孙关系
3.1. 结果集
3.1.1. sql
select ename,empno,mgr
from emp
where ename in ('KING','CLARK','MILLER')
ENAME EMPNO MGR
---------- ---------- ----------
CLARK 7782 7839
KING 7839
MILLER 7934 7782
3.1.2. sql
LEAF___BRANCH_ _ _ROOT
----------------------
MILLER-->CLARK-->KING
3.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 all
select 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 = 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
select ltrim(
sys_connect_by_path(ename,'-->'),
'-->') leaf___branch___root
from emp
where level = 3
start with ename = 'MILLER
connect by prior mgr = empno
3.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.empno
3.8.2. MySQL使用CONCAT函数
4. 创建层次视图
4.1. 结果集
4.1.1. sql
EMP_TREE
-------------------------------
KING
KING - BLAKE
KING - BLAKE - ALLEN
KING - BLAKE - JAMES
KING - BLAKE - MARTIN
KING - BLAKE - TURNER
KING - BLAKE - WARD
KING - CLARK
KING - CLARK - MILLER
KING - JONES
KING - JONES - FORD
KING - JONES - FORD - SMITH
KING - JONES - SCOTT
KING - JONES - SCOTT – ADAMS
4.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 1
4.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 null
connect by prior empno=mgr
order by 1
4.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'
union
select 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 1
4.7. MySQL
4.7.1. sql
select emp_tree
from (
select ename as emp_tree
from emp
where mgr is null
union
select concat(a.ename,' - ',b.ename)
from emp a
join
emp b on (a.empno=b.mgr)
where a.mgr is null
union
select 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'
union
select 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 1
5. 给定的父节点对应的所有子节点
5.1. 结果集
5.1.1. sql
ENAME
---------
JONES
SCOTT
ADAMS
FORD
SMITH
5.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 x
5.5. Oracle
5.5.1. CONNECT BY子句
5.5.1.1. sql
select ename
from emp
start with ename = 'JONES'
connect by prior empno = mgr
5.6. PostgreSQL
5.7. MySQL
5.8. 自连接
5.8.1. sql
create view v1
as
select ename,mgr,empno
from emp
where ename = 'JONES'
create view v2
as
select ename,mgr,empno
from emp
where mgr = (select empno from v1)
create view v3
as
select ename,mgr,empno
from emp
where mgr in (select empno from v2)
5.8.2. sql
select ename from v1
union
select ename from v2
union
select ename from v3
5.8.3. 需要提前知道层次关系的深度
6. 确认叶子节点、分支节点和根节点
6.1. 结果集
6.1.1. sql
ENAME IS_LEAF IS_BRANCH IS_ROOT
---------- ---------- ---------- ----------
KING 0 0 1
JONES 0 1 0
SCOTT 0 1 0
FORD 0 1 0
CLARK 0 1 0
BLAKE 0 1 0
ADAMS 1 0 0
MILLER 1 0 0
JAMES 1 0 0
TURNER 1 0 0
ALLEN 1 0 0
WARD 1 0 0
MARTIN 1 0 0
SMITH 1 0 0
6.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 desc
6.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 null
connect by prior empno = mgr
order by 4 desc, 3 desc
6.7.1.1. Oracle Database 10g新增的CONNECT_BY_ROOT和CONNECT_BY_ISLEAF
合集:
选读SQL经典实例
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业