建表和填充记录,
create table test(superid varchar2(20),id varchar2(20));insert into test values('1','11');
insert into test values('1','12');insert into test values('2','21');
insert into test values('2','22');insert into test values('11','111');
insert into test values('11','112');insert into test values('12','121');
insert into test values('12','122');insert into test values('21','211');
insert into test values('21','212');insert into test values('22','221');
insert into test values('22','222');commit;
想通过一个sql语句查询重如下结果:
ID superid topSuperid(最顶层ID)
1 0 0
111 11 1
211 21 2请问大家有方法么?
create table test(superid varchar2(20),id varchar2(20));insert into test values('1','11');
insert into test values('1','12');insert into test values('2','21');
insert into test values('2','22');insert into test values('11','111');
insert into test values('11','112');insert into test values('12','121');
insert into test values('12','122');insert into test values('21','211');
insert into test values('21','212');insert into test values('22','221');
insert into test values('22','222');commit;
想通过一个sql语句查询重如下结果:
ID superid topSuperid(最顶层ID)
1 0 0
111 11 1
211 21 2请问大家有方法么?
解决方案 »
- 表与视图能否join
- 动态Sql中round时间格式问题
- 如何提高以下SQL语句的执行效率
- 请教在oracle中通过定义包来实现结果集输出的问题?
- PL/SQL 批量插入2000万条数据
- 请问oracle 创建数据库时报 ora-1041 错误 如何解决
- 请指点迷津
- 关于ORACLE DIRECTORY MANAGER 的启动的问题
- 如果我有n个表, 我要比较2条记录的是否一致并找出不一致的.
- 运行动态Sql时出现错误,高分求教!
- 急:oracle数据库表空间的.dat文件删除后,还能删掉该表空间吗?
- 不显示删除回复显示所有回复显示星级回复显示得分回复 RedHat 5 安装 Oracle10g,运行runinstaller 出错(带乱码
select superid,'0' a,'0' b from test minus select id,'0','0' from test)
select id,superid,connect_by_root superid topSuperid from test
start with superid in (select superid from tt)
connect by prior id=superid
union all select * from tt
select a.id,a.superid,b.superid
from test a,(select id,superid from test) b
where a.superid = b.id
select a.id,a.superid,nvl(b.superid,0)
from test a,(select id,nvl(superid,0) superid from test) b
where a.superid = b.id(+)
主要不是很明白你需要什么,大致都能通过connect by实现
稍微麻烦的一点就是,你给出的测试表里,根节点id没有出现,需要从superid里提取
答:就指最底层的节点
没错,根节点就是通过superid一级一级的取,一直取到最顶端。