有两张表
a表
create table a
(aid varchar2(40),
name varchar2(100),
parentid varchar2(40)
);
b表
create table b
(aid varchar2(40),
operation varchar2(100)
);
a表数据如下
AID name parentid
420000000000 湖北省 000000000000
420200000000 湖北省黄石市 420000000000
420202000000 黄石市黄石港区 420200000000
420203000000 黄石市西塞山区 420200000000b表数据如下
AID operation
420000000000 12312321321希望得到结果
AID operation
420000000000 12312321321
420200000000 12312321321
420202000000 12312321321
420203000000 12312321321求大神支招,为了减少压力在b表只存储父ID,但是想得到父ID下所有子集的结果,想一个语句搞定
a表
create table a
(aid varchar2(40),
name varchar2(100),
parentid varchar2(40)
);
b表
create table b
(aid varchar2(40),
operation varchar2(100)
);
a表数据如下
AID name parentid
420000000000 湖北省 000000000000
420200000000 湖北省黄石市 420000000000
420202000000 黄石市黄石港区 420200000000
420203000000 黄石市西塞山区 420200000000b表数据如下
AID operation
420000000000 12312321321希望得到结果
AID operation
420000000000 12312321321
420200000000 12312321321
420202000000 12312321321
420203000000 12312321321求大神支招,为了减少压力在b表只存储父ID,但是想得到父ID下所有子集的结果,想一个语句搞定
oracle connect by prior id = id
有两张表
a表
create table a
(aid varchar2(40),
name varchar2(100),
parentid varchar2(40)
);
b表
create table b
(aid varchar2(40),
operation varchar2(100)
);
a表数据如下
AID name parentid
420000000000 湖北省 000000000000
420200000000 湖北省黄石市 420000000000
420202000000 黄石市黄石港区 420200000000
420203000000 黄石市西塞山区 420200000000b表数据如下
AID operation
420000000000 12312321321
420200000000 36565112121希望得到结果
AID operation
420000000000 12312321321
420200000000 12312321321
420202000000 12312321321
420203000000 12312321321
420200000000 36565112121
420202000000 36565112121
420203000000 36565112121求大神支招,为了减少压力在b表只存储父ID,但是想得到父ID下所有子集的结果,想一个语句搞定
from (select a.*, level, connect_by_root(a.aid) as rootid
from a
start with a.parentid = '000000000000'
connect by a.parentid = prior a.aid) t1,
b
where t1.rootid = b.aid;看看这个应该可以满足你要求
select a.*, level, connect_by_root(a.aid) as rootid,b.operation
from a,b
start with a.parentid = b.aid
connect by a.parentid = prior a.aid) t;