Oracle 9i判断是叶子或根节点,是比较麻烦的一件事情,SQL演示脚本如下:
- DROP TABLE idb_hierarchical;
- create TABLE idb_hierarchical
- (
- id number,
- parent_id number,
- str varchar2(10)
- );
- insert into idb_hierarchical values(1,null,'A');
- insert into idb_hierarchical values(2,1,'B');
- insert into idb_hierarchical values(3,2,'C');
- insert into idb_hierarchical values(4,3,'D');
- insert into idb_hierarchical values(5,2,'E');
- insert into idb_hierarchical values(6,2,'F');
- insert into idb_hierarchical values(7,3,'G');
- insert into idb_hierarchical values(8,4,'H');
- insert into idb_hierarchical values(9,4,'I');
- insert into idb_hierarchical values(10,null,'J');
- insert into idb_hierarchical values(11,10,'K');
- insert into idb_hierarchical values(12,11,'L');
- insert into idb_hierarchical values(13,10,'M');
- DROP TABLE idb_hierarchical;
- create TABLE idb_hierarchical
- (
- id number,
- parent_id number,
- str varchar2(10)
- );
- insert into idb_hierarchical values(1,null,'A');
- insert into idb_hierarchical values(2,1,'B');
- insert into idb_hierarchical values(3,2,'C');
- insert into idb_hierarchical values(4,3,'D');
- insert into idb_hierarchical values(5,2,'E');
- insert into idb_hierarchical values(6,2,'F');
- insert into idb_hierarchical values(7,3,'G');
- insert into idb_hierarchical values(8,4,'H');
- insert into idb_hierarchical values(9,4,'I');
- insert into idb_hierarchical values(10,null,'J');
- insert into idb_hierarchical values(11,10,'K');
- insert into idb_hierarchical values(12,11,'L');
- insert into idb_hierarchical values(13,10,'M');
示例数据清单如下:
- SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL
- FROM idb_hierarchical
- START WITH PARENT_ID IS NULL
- CONNECT BY PARENT_ID = PRIOR ID;
- SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL
- FROM idb_hierarchical
- START WITH PARENT_ID IS NULL
- CONNECT BY PARENT_ID = PRIOR ID;
表1:数据清单
STR_LEVEL |
ID |
PARENT_ID |
LVL |
+..A |
1 |
|
1 |
+….B |
2 |
1 |
2 |
+……C |
3 |
2 |
3 |
+……..D |
4 |
3 |
4 |
+……….H |
8 |
4 |
5 |
+……….I |
9 |
4 |
5 |
+……..G |
7 |
3 |
4 |
+……E |
5 |
2 |
3 |
+……F |
6 |
2 |
3 |
+..J |
10 |
|
1 |
+….K |
11 |
10 |
2 |
+……L |
12 |
11 |
3 |
+….M |
13 |
10 |
2 |
在表1中,ID为8、9、 7、5、6、12、13都没有子节点,因此称为叶节点。
1.oracle9i 查询叶节点
只显示叶子节点SQL
- SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL
- FROM idb_hierarchical I
- --在oracle 9i中显示叶节点,需要判断是否有子节点即可
- WHERE NOT EXISTS(SELECT 1
- FROM idb_hierarchical B
- WHERE I.ID=B.PARENT_ID)
- START WITH PARENT_ID IS NULL
- CONNECT BY PARENT_ID = PRIOR ID;
- SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL
- FROM idb_hierarchical I
- --在oracle 9i中显示叶节点,需要判断是否有子节点即可
- WHERE NOT EXISTS(SELECT 1
- FROM idb_hierarchical B
- WHERE I.ID=B.PARENT_ID)
- START WITH PARENT_ID IS NULL
- CONNECT BY PARENT_ID = PRIOR ID;
表2
STR_LEVEL |
ID |
PARENT_ID |
LVL |
+……….H |
8 |
4 |
5 |
+……….I |
9 |
4 |
5 |
+……..G |
7 |
3 |
4 |
+……E |
5 |
2 |
3 |
+……F |
6 |
2 |
3 |
+……L |
12 |
11 |
3 |
+….M |
13 |
10 |
2 |
显示所有节点,标明该行是否为叶节点SQL
- SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL,
- NVL((SELECT 'N'
- FROM idb_hierarchical B
- WHERE I.ID=B.PARENT_ID
- AND ROWNUM < 2),'Y') IS_LEAF
- FROM idb_hierarchical I
- START WITH PARENT_ID IS NULL
- CONNECT BY PARENT_ID = PRIOR ID;
- SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL,
- NVL((SELECT 'N'
- FROM idb_hierarchical B
- WHERE I.ID=B.PARENT_ID
- AND ROWNUM < 2),'Y') IS_LEAF
- FROM idb_hierarchical I
- START WITH PARENT_ID IS NULL
- CONNECT BY PARENT_ID = PRIOR ID;
表3
STR_LEVEL |
ID |
PARENT_ID |
LVL |
IS_LEAF |
+..A |
1 |
|
1 |
N |
+....B |
2 |
1 |
2 |
N |
+......C |
3 |
2 |
3 |
N |
+........D |
4 |
3 |
4 |
N |
+..........H |
8 |
4 |
5 |
Y |
+..........I |
9 |
4 |
5 |
Y |
+........G |
7 |
3 |
4 |
Y |
+......E |
5 |
2 |
3 |
Y |
+......F |
6 |
2 |
3 |
Y |
+..J |
10 |
|
1 |
N |
+....K |
11 |
10 |
2 |
N |
+......L |
12 |
11 |
3 |
Y |
+....M |
13 |
10 |
2 |
Y |
oracle 9i 查询根节点
- SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL
- FROM idb_hierarchical I
- START WITH id =2
- CONNECT BY PARENT_ID = PRIOR ID;
- SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL
- FROM idb_hierarchical I
- START WITH id =2
- CONNECT BY PARENT_ID = PRIOR ID;
表4
STR_LEVEL |
ID |
PARENT_ID |
LVL |
+..B |
2 |
1 |
1 |
+....C |
3 |
2 |
2 |
+......D |
4 |
3 |
3 |
+........H |
8 |
4 |
4 |
+........I |
9 |
4 |
4 |
+......G |
7 |
3 |
3 |
+....E |
5 |
2 |
2 |
+....F |
6 |
2 |
2 |
根节点ID应该为3、5、6,即lvl为1即可
查询根节点,只显示根节点SQL
- SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,
- ID,
- PARENT_ID,
- LEVEL LVL,
- (select b.str
- from idb_hierarchical b
- where level = 1
- start with b.id = 2
- connect by prior b.id = b.parent_id
- ) root_str
- FROM idb_hierarchical I
- where level = 1
- START WITH id = 2
- CONNECT BY PARENT_ID = PRIOR ID;
- SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,
- ID,
- PARENT_ID,
- LEVEL LVL,
- (select b.str
- from idb_hierarchical b
- where level = 1
- start with b.id = 2
- connect by prior b.id = b.parent_id
- ) root_str
- FROM idb_hierarchical I
- where level = 1
- START WITH id = 2
- CONNECT BY PARENT_ID = PRIOR ID;
表5
STR_LEVEL |
ID |
PARENT_ID |
LVL |
ROOT_STR |
+..B |
2 |
1 |
1 |
B |
标明根节点SQL
- SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,
- ID,
- PARENT_ID,
- DECODE(LEVEL, 1, 'Y', 'N') is_root,
- LEVEL LVL,
- (select b.str
- from idb_hierarchical b
- where level = 1
- start with b.id = 2
- connect by prior b.id = b.parent_id) root_str
- FROM idb_hierarchical I
- START WITH id = 2
- CONNECT BY PARENT_ID = PRIOR ID;
- SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,
- ID,
- PARENT_ID,
- DECODE(LEVEL, 1, 'Y', 'N') is_root,
- LEVEL LVL,
- (select b.str
- from idb_hierarchical b
- where level = 1
- start with b.id = 2
- connect by prior b.id = b.parent_id) root_str
- FROM idb_hierarchical I
- START WITH id = 2
- CONNECT BY PARENT_ID = PRIOR ID;
表6
STR_LEVEL |
ID |
PARENT_ID |
IS_ROOT |
LVL |
ROOT_STR |
+..B |
2 |
1 |
Y |
1 |
B |
+....C |
3 |
2 |
N |
2 |
B |
+......D |
4 |
3 |
N |
3 |
B |
+........H |
8 |
4 |
N |
4 |
B |
+........I |
9 |
4 |
N |
4 |
B |
+......G |
7 |
3 |
N |
3 |
B |
+....E |
5 |
2 |
N |
2 |
B |
+....F |
6 |
2 |
N |
2 |
B |
在oracle 10g提供了connect_by_isleaf和connect_by_root
oracle 10g用connect_by_isleaf判断叶节点
- SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL
- FROM idb_hierarchical I
- where connect_by_isleaf=1
- START WITH PARENT_ID IS NULL
- CONNECT BY PARENT_ID = PRIOR ID;
- SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL
- FROM idb_hierarchical I
- where connect_by_isleaf=1
- START WITH PARENT_ID IS NULL
- CONNECT BY PARENT_ID = PRIOR ID;
表7
STR_LEVEL |
ID |
PARENT_ID |
LVL |
+..........H |
8 |
4 |
5 |
+..........I |
9 |
4 |
5 |
+........G |
7 |
3 |
4 |
+......E |
5 |
2 |
3 |
+......F |
6 |
2 |
3 |
+......L |
12 |
11 |
3 |
+....M |
13 |
10 |
2 |
- SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL,
- decode(connect_by_isleaf,1,'Y','N') IS_LEAF
- FROM idb_hierarchical I
- START WITH PARENT_ID IS NULL
- CONNECT BY PARENT_ID = PRIOR ID;
- SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL,
- decode(connect_by_isleaf,1,'Y','N') IS_LEAF
- FROM idb_hierarchical I
- START WITH PARENT_ID IS NULL
- CONNECT BY PARENT_ID = PRIOR ID;
表8
STR_LEVEL |
ID |
PARENT_ID |
LVL |
IS_LEAF |
+..A |
1 |
|
1 |
N |
+....B |
2 |
1 |
2 |
N |
+......C |
3 |
2 |
3 |
N |
+........D |
4 |
3 |
4 |
N |
+..........H |
8 |
4 |
5 |
Y |
+..........I |
9 |
4 |
5 |
Y |
+........G |
7 |
3 |
4 |
Y |
+......E |
5 |
2 |
3 |
Y |
+......F |
6 |
2 |
3 |
Y |
+..J |
10 |
|
1 |
N |
+....K |
11 |
10 |
2 |
N |
+......L |
12 |
11 |
3 |
Y |
+....M |
13 |
10 |
2 |
Y |
oracle 10g用connect_by_root判断根节点
- SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,
- ID,
- PARENT_ID,
- LEVEL LVL,
- connect_by_root STR ROOT_STR
- FROM idb_hierarchical I
- START WITH id = 2
- CONNECT BY PARENT_ID = PRIOR ID;
- SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,
- ID,
- PARENT_ID,
- LEVEL LVL,
- connect_by_root STR ROOT_STR
- FROM idb_hierarchical I
- START WITH id = 2
- CONNECT BY PARENT_ID = PRIOR ID;
表9
STR_LEVEL |
ID |
PARENT_ID |
LVL |
ROOT_STR |
+..B |
2 |
1 |
1 |
B |
+....C |
3 |
2 |
2 |
B |
+......D |
4 |
3 |
3 |
B |
+........H |
8 |
4 |
4 |
B |
+........I |
9 |
4 |
4 |
B |
+......G |
7 |
3 |
3 |
B |
+....E |
5 |
2 |
2 |
B |
+....F |
6 |
2 |
2 |
B |
- SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,
- ID,
- PARENT_ID,
- DECODE(LEVEL, 1, 'Y', 'N') is_root,
- LEVEL LVL,
- connect_by_root STR ROOT_STR
- FROM idb_hierarchical I
- START WITH id = 3
- CONNECT BY PARENT_ID = PRIOR ID;
- SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,
- ID,
- PARENT_ID,
- DECODE(LEVEL, 1, 'Y', 'N') is_root,
- LEVEL LVL,
- connect_by_root STR ROOT_STR
- FROM idb_hierarchical I
- START WITH id = 3
- CONNECT BY PARENT_ID = PRIOR ID;
表10
STR_LEVEL |
ID |
PARENT_ID |
IS_ROOT |
LVL |
ROOT_STR |
+..C |
3 |
2 |
Y |
1 |
C |
+....D |
4 |
3 |
N |
2 |
C |
+......H |
8 |
4 |
N |
3 |
C |
+......I |
9 |
4 |
N |
3 |
C |
+....G |
7 |
3 |
N |
2 |
C |
- SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,
- ID,
- PARENT_ID,
- DECODE(LEVEL, 1, 'Y', 'N') is_root,
- LEVEL LVL,
- connect_by_root STR ROOT_STR
- FROM idb_hierarchical I
- START WITH PARENT_ID = 2
- CONNECT BY PARENT_ID = PRIOR ID;
- SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,
- ID,
- PARENT_ID,
- DECODE(LEVEL, 1, 'Y', 'N') is_root,
- LEVEL LVL,
- connect_by_root STR ROOT_STR
- FROM idb_hierarchical I
- START WITH PARENT_ID = 2
- CONNECT BY PARENT_ID = PRIOR ID;
表11
STR_LEVEL |
ID |
PARENT_ID |
IS_ROOT |
LVL |
ROOT_STR |
+..C |
3 |
2 |
Y |
1 |
C |
+....D |
4 |
3 |
N |
2 |
C |
+......H |
8 |
4 |
N |
3 |
C |
+......I |
9 |
4 |
N |
3 |
C |
+....G |
7 |
3 |
N |
2 |
C |
+..E |
5 |
2 |
Y |
1 |
E |
+..F |
6 |
2 |
Y |
1 |
F |
最新文章
- Javascript学习笔记:2种其他类型转换为数字Number类型的方式
- js截取字符串显示引号两种方法
- OAUTH 协议介绍
- PHP如何解决网站大流量与高并发的问题
- Java的加密与解密
- 第六章 管理类型(In .net4.5) 之 创建类型
- Individual Contest #1 and Private Training #1
- servlet的提交
- 转:基于科大讯飞语音API语音识别开发详解
- [虚拟化/云][全栈demo] 为qemu增加一个PCI的watchdog外设(七)
- web工程中地址的写法
- linux下编译安装nginx
- Python连接webstocker获取消息
- 【BZOJ1305】跳舞(网络流)
- PostgreSQL安装和使用
- I/O复用之epoll
- FPGA管脚分配文件保存方法
- LockSupport分析
- js 事件冒泡、事件捕获及事件委托
- OpenCV的Rect矩形类用法
热门文章
- Docker : endpoint with name xxx already exists.
- hashCode和identityHashCode 的关系
- Tomcat7并发和线程数
- Sails入门指南
- CentOS 7.4 使用源码包编译安装MySQL 5.7.20
- matlab入门笔记(七):数据文件I/O
- Java 8 Lambda排序 : Comparator example
- Latex中如何设置字体颜色(3种方式)
- xbox360 双65厚机自制系统无硬盘 U盘玩游戏方法
- 使用OkHttpClient处理json请求处理的方式