Oracle SQL

Now there are
The create table tmp_dept (dept varchar2 (10), id varchar2 (5));

The begin
Insert into tmp_dept values (' dept1 ', '1');
Insert into tmp_dept values (' dept1 ', '2');
Insert into tmp_dept values (' dept2 ', '1');
Insert into tmp_dept values (' dept2 ', '2');
Insert into tmp_dept values (' dept2 ', '3');
Insert into tmp_dept values (' dept3 ', '1');
end;

No index, how to optimize the code below, or have a better deal, tmp_dept actual table data is larger than 200000
The select a. d. ept, Anderson d, Anderson d | | '/' | | b.i d
The from tmp_dept a, tmp_dept b
Where a. d. ept=b.d ept and Anderson d<> B.i d;

CodePudding user response:

Are you want
Dept1 1/2
Dept2 1/2/3
Dept3 1

Or
Dept1 1/2
2/1 dept1
Dept2 1/2
A third dept2
2/1 dept2
Two-thirds dept2
3/1 dept2
Dept2 3/2

CodePudding user response:

reference 1st floor zhuomingwang response:

are you wantDept1 1/2
Dept2 1/2/3
Dept3 1

Or
Dept1 1/2
2/1 dept1
Dept2 1/2
A third dept2
2/1 dept2
Two-thirds dept2
3/1 dept2
Dept2 3/2

To get the second result
Dept1 1/2
2/1 dept1
Dept2 1/2
A third dept2
2/1 dept2
Two-thirds dept2
3/1 dept2
Dept2 3/2

CodePudding user response:

That from the simple to rewrite the SQL now didn't find good method on
Can try indexes have any effect on the dept

CodePudding user response:

Select * from (
Select a t1. Dept, sys_connect_by_path (id, '/'), level lv from tmp_dept t1 connect by level & lt;=2 and the prior t1. Dept=t1. Dept and the prior t1. The id!=t1. Id
) t1 where t1. Lv=2