SQL²éѯÎÊÌ⣨oracle) - Oracle / ¿ª·¢

ÈçÏÂ2ÕÅ±í£º
table1:
node_id
parent_id
root_id
value

table2:
node_id
status

±í1´æ´¢ÁËÒ»¿ÃÊ÷£¬±í2´æ´¢Á˽ڵã״̬£¬ÎÊÌâÈçÏ£º

ÈçºÎÓÃSQLÓï¾ä»ñµÃ״̬statusΪ1µÄ½ÚµãµÄËùÓÐ×Ó½ÚµãÒÔ¼°ÆäÏÂ×ÓÊ÷ÖÐËùÓнڵãµÄvalue£¿

Çó½â¡£ÓÉÓڿ͹ÛÒòËØÖ»ÄÜÓÃsqlʵÏÖ£¬²»È»¾ÍµÝ¹éÁË¡£¡£¡£¶àл´ó¼Ò

---

select a.node_id,a.value
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

---

UP£¬Õâ¸öÐèÒªÓõ½ start...wiht connect by Óï·¨£¬²ã´Î²éѯ¡£

ÒýÓÃ

select a.node_id,a.value
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
start with b.status=1
connect by prior a.node_id=a.parent_id

¶àл»Ø¸´£¡²»¹ý±¨ sql command not properly ended ´íÎó£¬ÊÔÁ˺ܶà˳Ðò»¹ÊDz»ÐУ¬Íû½â´ð£¬Ð»Ð»ÁË

---

SQL code:

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,statu