- SQL code
A表type_id name parent_id 1 a 0 2 b 0 3 c 0 4 b1 2 5 b2 2
- SQL code
B表 id name type_id 1 tt 4 2 yy 5
求type_id 为2 的所有子元素(也就是找tt,yy)， 在线等······
------解决方案--------------------
这个需要使用connect by ,提供相关示例,自己参照着修改:
- SQL code
--1.Hierarchical Queries: START WITH and CONNECT BY PRIOR clauses --Hierarchical Queries--START WITH and CONNECT BY PRIOR clauses.SELECT employee_id, manager_id, first_name, last_nameFROM employee_jhSTART WITH employee_id = 1CONNECT BY PRIOR employee_id = manager_id;EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME----------- ---------- ---------- ---------- 1 0 James Smith 2 1 Ron Johnson 3 2 Fred Hobbs 5 2 Rob Green 4 1 Susan Jones 6 4 Jane Brown 9 6 Henry Heyson 7 4 John Grey 8 7 Jean Blue 10 1 Kevin Black 11 10 Keith Long 12 10 Frank Howard 13 10 Doreen Penn13 rows selected.--2.Using a Subquery in a START WITH ClauseSELECT LEVEL, LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' || last_name AS employeeFROM employee_jhSTART WITH employee_id = (SELECT employee_id FROM employee_jh WHERE first_name = 'Kevin' AND last_name = 'Black')CONNECT BY PRIOR employee_id = manager_id; LEVEL EMPLOYEE---------- ------------------------- 1 Kevin Black 2 Keith Long 2 Frank Howard 2 Doreen Penn--3.Including Other Conditions in a Hierarchical QuerySELECT LEVEL, LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' || last_name AS employee, salaryFROM employee_jhWHERE salary <= 50000START WITH employee_id = 1CONNECT BY PRIOR employee_id = manager_id; LEVEL EMPLOYEE SALARY---------- ------------------------- ---------- 3 Rob Green 40000 3 Jane Brown 45000 4 Henry Heyson 30000 3 John Grey 30000 4 Jean Blue 29000 3 Keith Long 50000 3 Frank Howard 45000 3 Doreen Penn 470008 rows selected.
------解决方案--------------------
select B.name,B.type_id,A.parent_id from A left outer join B on
(A.type_id = b.type_id) where A.parent_id = 2;
不知道你是 只是查询 还是要写递归
------解决方案--------------------
- SQL code
with a as(select 1 type_id,'a' name,0 parent_id from dualunion allselect 2,'b',0 from dualunion allselect 3,'c',0 from dualunion allselect 4,'b1',2 from dualunion allselect 5,'b2',2 from dual),b as(select 1 id,'tt' name,4 type_id from dualunion allselect 2,'yy',5 from dual)select type_id from b where exists (select 1 from (select type_id from a where type_id<>2 connect by prior type_id=parent_id start with type_id=2) c where c.type_id=b.type_id) TYPE_ID---------- 4 5
------解决方案--------------------
- SQL code
SELECT * FROM tb_ACONNECT BYPRIOR type_id = parent_id START WITH type_id = '2'