How do you write this recursion?-CodePudding

Table has two tables, A department, storage department ID name superior departments; B table store employees table ID name and department ID
Now I hope that through an SQL statement to achieve all of the top departments of employees (including junior staff)

Zhang SAN's top department
Li si's top department
Department of top fifty
Zhao six top department
Top eight departments money

CodePudding user response:

The building Lord baidu the oracle connect by the prior

Have you as a result,

CodePudding user response:

reference 1st floor wmxcn2000 response:

the building Lord baidu the oracle connect by the prior

Will have you as a result, the

Have tried, can't

CodePudding user response:

Posted on the test data and expected results,

In addition, can also post your statement, and said where is no good,

CodePudding user response:

refer to the second floor Alex_V3T response:

Quote: refer to 1st floor wmxcn2000 response:

The building Lord baidu the oracle connect by the prior

Will have you as a result, the

Have tried, no

Post to build table statements of A and B, have the insert statement is best,
I help you to write SQL,

CodePudding user response:

A problem is very simple, using the recursive down, about three five lines of code to achieve,

CodePudding user response:

 - has two table, table, A department store department ID name superior departments; B table store employees table ID name and department ID 
- now I hope that through an SQL statement to achieve all of the top departments of employees (including junior staff) 
- drop table dept; 
The create table dept (
Dept_id varchar (5), 
Up_id varchar (5), 
Misc varchar (20), 
Primary key (dept_id) 
); 
INSERT INTO DEPT (DEPT_ID, UP_ID, MISC) VALUES (' 12 ', '-', '2' top department); 
INSERT INTO DEPT (DEPT_ID, UP_ID, MISC) VALUES (' 1 ', '-', 'top departments'); 
INSERT INTO DEPT (DEPT_ID, UP_ID, MISC) VALUES (' 01 ', '1', '1'); 
INSERT INTO DEPT (DEPT_ID, UP_ID, MISC) VALUES (' 02 ', '1', '2'); 
INSERT INTO DEPT (DEPT_ID, UP_ID, MISC) VALUES (' 003 ', '02', '3'); 
- drop table employ; 
The create table employ (
Emp_id varchar (6), 
The name varchar (20), 
Dept_id varchar (5), 
Primary key (emp_id) 
); 
INSERT INTO EMPLOY (EMP_ID, NAME, DEPT_ID) VALUES (' 00001 ', 'money 8', '1'); 
INSERT INTO EMPLOY (EMP_ID, NAME, DEPT_ID) VALUES (' 00002 ', 'zhao 6', '01); 
INSERT INTO EMPLOY (EMP_ID, NAME, DEPT_ID) VALUES (' 00003 ', 'Cathy', '02'); 
INSERT INTO EMPLOY (EMP_ID, NAME, DEPT_ID) VALUES (' 00004 ', 'bill', '02'); 
INSERT INTO EMPLOY (EMP_ID, NAME, DEPT_ID) VALUES (' 00005 ', 'zhang', '003'); 


- query is 1 - top departments all employees under the 
With temp (emp_id, name, dept_id) as (
The select e.e mp_id, e.n ame, e.d ept_id from employ e left join dept d on e.d ept_id=d.d ept_id where e.d ept_id='02' 
Union all 
Select d2 emp_id, d2. The name, d2, dept_id from temp t, 
(select emp_id, name, e.d ept_id, d3. Up_id from employ e, dept d3 where e.d ept_id=d3. Dept_id) d2 where t.d ept_id=d2. Up_id 
) 
Select distinct (emp_id), name, dept_id from temp order by emp_id;

CodePudding user response:

The above conditions to write wrong

 with temp (emp_id, name, dept_id) as (
The select e.e mp_id, e.n ame, e.d ept_id from employ e left join dept d on e.d ept_id=d.d ept_id where e.d ept_id='1' 
Union all 
Select d2 emp_id, d2. The name, d2, dept_id from temp t, 
(select emp_id, name, e.d ept_id, d3. Up_id from employ e, dept d3 where e.d ept_id=d3. Dept_id) d2 where t.d ept_id=d2. Up_id 
) 
Select distinct (emp_id), name, dept_id from temp order by emp_id;

CodePudding user response:

 
- 1. To create a test table 
The create table t1 (depno varchar2 (10), depname varchar2 (100), pre_depno varchar2 (10)); 
The create table t2 (empno varchar2 (10), empname varchar2 (100), depno varchar2 (10)); 

- 2. Add test statement 
Insert into t1 values (' 001 ', 'top department, null); 
Insert into t1 values (' 010 ', '1', '001'); 
Insert into t1 values (' 020 ', '2', '001'); 
Insert into t1 values (' 030 ', '3', '020'); 


Insert into t2 values (1, 'zhao 6', '010'); 
Insert into t2 values (2, 'bill', '020'); 
Insert into t2 values (3, 'Cathy', '020'); 
Insert into t2 values (4, 'zhang', '030'); 
Insert into t2 values (5, '8' money, '001'); 
commit; 

SQL realize 
 - 3.Select a t1 depname, t2. Empname 
The from t1, t2 
Where a t1. Depno=t2. Depno 
Start with t1. Pre_depno is null 
Connect by the prior t1. Depno=t1. Pre_depno

CodePudding user response:

The problems or tree