oracle层次查询
来源:互联网 发布:二维火软件招聘 编辑:程序博客网 时间:2024/05/21 11:35
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');
示例数据清单如下:
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
阅读全文
0 0
- oracle层次查询
- Oracle 层次查询
- oracle的层次查询
- ORACLE层次化查询
- oracle 层次化查询
- Oracle层次查询
- ORACLE层次查询学习
- oracle 层次查询
- Oracle 层次查询
- oracle 层次查询
- ORACLE之 层次查询
- Oracle当中层次查询
- oracle层次查询
- Oracle层次查询
- oracle 层次查询
- oracle层次化查询
- Oracle层次化查询
- oracle的层次查询
- 软银领投Petuum 9300万美元融资,CMU邢波想让AI走进普通企业
- 12年后,索尼重启机器人业务
- 英特尔生产17个量子位超导芯片,现已交付合作伙伴
- 吴恩达悄然发布AI维基,另外他的技术岗已经招满了
- 斯坦福公布3D街景数据集:2500万张图像,8个城市模型 | 下载
- oracle层次查询
- 决策树基本理论学习以及Python代码实现和详细注释
- 学前端VS考研
- 微信小程序 wx.navigateTo和wx.redirectTo 不跳转问题
- 机器学习基本概念(1)--绪论
- hdu1825 Xor Sum
- 阿里巴巴的AI革命 | 4天云栖大会干货总结
- 两个月入门深度学习,全靠动手实践!一位前端小哥的经验分享
- 国内首个深度学习开发SDK发布:深鉴科技对标英伟达TensorRT