不晓得是不是你说的意思select id,wm_concat(外键) from table group by id; 树形结构 一般的设计是 id 父级id。这样便于构造树形结构。
create table a ( serial varchar2(10) primary key )insert into a values('main_01') / insert into a values('main_02') /create table b ( childSerial varchar2(10), father varchar2(10) references a(serial) )insert into b values('child001','main_01') / insert into b values('child002','main_01') / insert into b values('child003','main_01') / insert into b values('child004','main_02') / insert into b values('child005','main_02') / 为了在tabel里实现树形菜单式的显示效果,我需要将主键信息和外键信息放到一个列里以上面2个表里的数据为例,前台传入一个serial, 假设是main_01,那么我想让main_01,以及以main_01为主键的信息:child001、child002、child003,在一个查询语句select中,显示在1列里如果传入的是main_02,那么,main_02、child004、child005三条信息显示在一列里我想过start with connect by prior ,不知道可不可以,我对Oracle的这个树查询只是知道,不会用哪位前辈能不能以上面我给的2张表的数据,写个例子?
with a as ( select 'main_01' as serial from dual union all select 'main_02' as serial from dual ), b as ( select 'child001' as childSerial, 'main_01' as father from dual union all select 'child002' as childSerial, 'main_01' as father from dual union all select 'child003' as childSerial, 'main_01' as father from dual union all select 'child004' as childSerial, 'main_02' as father from dual union all select 'child005' as childSerial, 'main_02' as father from dual ) select serial, stragg(childSerial) as childen from ( select * from a, b where a.serial = b.father ) group by serial
group by id;
树形结构 一般的设计是 id 父级id。这样便于构造树形结构。
(
serial varchar2(10) primary key
)insert into a values('main_01')
/
insert into a values('main_02')
/create table b
(
childSerial varchar2(10),
father varchar2(10) references a(serial)
)insert into b values('child001','main_01')
/
insert into b values('child002','main_01')
/
insert into b values('child003','main_01')
/
insert into b values('child004','main_02')
/
insert into b values('child005','main_02')
/
为了在tabel里实现树形菜单式的显示效果,我需要将主键信息和外键信息放到一个列里以上面2个表里的数据为例,前台传入一个serial, 假设是main_01,那么我想让main_01,以及以main_01为主键的信息:child001、child002、child003,在一个查询语句select中,显示在1列里如果传入的是main_02,那么,main_02、child004、child005三条信息显示在一列里我想过start with connect by prior ,不知道可不可以,我对Oracle的这个树查询只是知道,不会用哪位前辈能不能以上面我给的2张表的数据,写个例子?
(
select 'main_01' as serial from dual
union all
select 'main_02' as serial from dual
),
b as
(
select 'child001' as childSerial, 'main_01' as father from dual
union all
select 'child002' as childSerial, 'main_01' as father from dual
union all
select 'child003' as childSerial, 'main_01' as father from dual
union all
select 'child004' as childSerial, 'main_02' as father from dual
union all
select 'child005' as childSerial, 'main_02' as father from dual
)
select serial, stragg(childSerial) as childen from
(
select * from a, b where a.serial = b.father
)
group by serial