Question

oracle里的递归查询怎么写？

Answer 1

关键字：START WITH ... CONNECT BY PRIOR

select t.*,level from table_test t
start with p_id = '3'
connect by p_id = prior s_id; --查询p_id为3的所有子孙id

select t.*,level from table_test t
start with p_id = '3'
connect by prior p_id = s_id; --查询p_id为3的所有父辈id

Answer 2

CREATE TABLE SC_DISTRICT
(
ID NUMBER(10) NOT NULL,
PARENT_ID NUMBER(10),
NAME VARCHAR2(255 BYTE) NOT NULL
);

ALTER TABLE SC_DISTRICT ADD (
CONSTRAINT SC_DISTRICT_PK
PRIMARY KEY
(ID));

ALTER TABLE SC_DISTRICT ADD (
CONSTRAINT SC_DISTRICT_R01
FOREIGN KEY (PARENT_ID)
REFERENCES SC_DISTRICT (ID));

INSERT INTO SC_DISTRICT(ID,NAME) VALUES(1,'湖北省');

INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(2,1,'武汉市');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(3,1,'黄冈市');

INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(4,2,'武昌区');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(5,2,'汉口镇');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(6,2,'黄梅县');
查询武汉市下面的所有行政组织(结果包含当前节点):
SELECT *
FROM SC_DISTRICT
START WITH NAME='武汉市'
CONNECT BY PRIOR ID=PARENT_ID

IT JOB 远 标