如下2张表:
table1:
node_id
parent_id
root_id
valuetable2:
node_id
status表1存储了一棵树,表2存储了节点状态,问题如下:如何用SQL语句获得状态status为1的节点的所有子节点以及其下子树中所有节点的value?求解。由于客观因素只能用sql实现,不然就递归了多谢大家
table1:
node_id
parent_id
root_id
valuetable2:
node_id
status表1存储了一棵树,表2存储了节点状态,问题如下:如何用SQL语句获得状态status为1的节点的所有子节点以及其下子树中所有节点的value?求解。由于客观因素只能用sql实现,不然就递归了多谢大家
from table1 a,table2 b
where a.node_id=b.node_id
start with b.status=1
connect by prior a.node_id=a.parent_id
多谢回复!不过报 sql command not properly ended 错误,试了很多顺序还是不行,望解答,谢谢了
create table tb(id int,pid int);
insert into tb select 1,0 from dual;
insert into tb select 2,0 from dual;
insert into tb select 3,1 from dual;
insert into tb select 4,1 from dual;
insert into tb select 5,3 from dual;
create table tc(id int,status int);
insert into tc select 1,1 from dual;
insert into tc select 2,0 from dual;
insert into tc select 3,1 from dual;
insert into tc select 4,1 from dual;
insert into tc select 5,1 from dual;commit;
select * from tc a,tc b
where a.id = b.id and b.status=1
connect by prior a.id=a.pid;
from table1 a,table2 b
where a.node_id=b.node_id
start with b.status=1
connect by prior a.node_id=a.parent_id 修改为:
select a.node_id,a.value
from table1 a,table2 b
where a.node_id=b.node_id
and b.status=1
connect by prior a.node_id=a.parent_id